Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy values from a cell based on values of another cell
Hello,
I have an excel file with multiple sheets, here are the colum headings from sheet 1, B C D E F Date Amt Desc Category Total What I want to do on sheet 2 is check for value "Y" in the Desc column on sheet 1, if the value ="Y" then report the value in the Date column on sheet 1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B) function, but I only want to see the values that are = to "Y", when I populate the if function, I get values for each row. In other words I only want it to report the date column if the Desc column = Y. Does this make sense? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy values from a cell based on values of another cell
Try this:
=SUMIF(D:D,"Y",B:B) -- "Actually, I AM a rocket scientist." -- JB "Spence10169" wrote: Hello, I have an excel file with multiple sheets, here are the colum headings from sheet 1, B C D E F Date Amt Desc Category Total What I want to do on sheet 2 is check for value "Y" in the Desc column on sheet 1, if the value ="Y" then report the value in the Date column on sheet 1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B) function, but I only want to see the values that are = to "Y", when I populate the if function, I get values for each row. In other words I only want it to report the date column if the Desc column = Y. Does this make sense? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy values from a cell based on values of another cell
Or rather:
=SUMIF(Sheet1!D:D,"Y",Sheet1!B:B) -- "Actually, I AM a rocket scientist." -- JB "Spence10169" wrote: Hello, I have an excel file with multiple sheets, here are the colum headings from sheet 1, B C D E F Date Amt Desc Category Total What I want to do on sheet 2 is check for value "Y" in the Desc column on sheet 1, if the value ="Y" then report the value in the Date column on sheet 1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B) function, but I only want to see the values that are = to "Y", when I populate the if function, I get values for each row. In other words I only want it to report the date column if the Desc column = Y. Does this make sense? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy values from a cell based on values of another cell
I put in the formula, but it isn't what I am looking for. That formula gave
me a sum based on conditions, basically I am looking for a list. Basically what I am trying to achieve can be done using the filter function, however I want sheet 2 to populate when I enter values on sheet 1, without using filters. "JBeaucaire" wrote: Or rather: =SUMIF(Sheet1!D:D,"Y",Sheet1!B:B) -- "Actually, I AM a rocket scientist." -- JB "Spence10169" wrote: Hello, I have an excel file with multiple sheets, here are the colum headings from sheet 1, B C D E F Date Amt Desc Category Total What I want to do on sheet 2 is check for value "Y" in the Desc column on sheet 1, if the value ="Y" then report the value in the Date column on sheet 1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B) function, but I only want to see the values that are = to "Y", when I populate the if function, I get values for each row. In other words I only want it to report the date column if the Desc column = Y. Does this make sense? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy values from a cell based on values of another cell
Okay, that's more difficult. A self-creating list on Sheet2 using the same
idea as a VLOOKUP does for 1 value/cell, you want a list created the same way. http://home.pacbell.net/beban/ That website offers some user-defined-functions that all inter-relate. The one you want to use here is VLOOKUPS. It's an array formula. You will need to copy the code for VLOOKUPS and a few other supporting functions into a module in your workbook. Copy the code for the functions: VLookups ArrayCountIf ArrayDimensions MakeArray Then, on sheet2, enter a standard VLOOKUP type formula: =VLOOKUPS("Y",Sheet1!$B$2:$D$10000,3) When you press Enter, a message will appear int he cell telling you how many cells downward you need to select. Highlight the cell and enough below to complete the requested number, press F2, then CTRL-SHIFT-ENTER to activate the array. Two things, first - you will need to be OK swapping the two columns. VLookup needs the column to search on the left. My formula above assumes you will swap the two columns. Second - since you want this summary to fill itself out as you go, even though it says "Select at least 100 rows" or whatever, go ahead and go down much further. The list will expand automatically as you add to the source chart on Sheet1. Hope this works as well for you as it does for me, I use this UDF for several sheets of my own. - Jerry -- "Actually, I AM a rocket scientist." -- JB "Spence10169" wrote: I put in the formula, but it isn't what I am looking for. That formula gave me a sum based on conditions, basically I am looking for a list. Basically what I am trying to achieve can be done using the filter function, however I want sheet 2 to populate when I enter values on sheet 1, without using filters. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to assign values to a cell based on values in another cell? | Excel Worksheet Functions | |||
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets | Excel Discussion (Misc queries) | |||
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets | Excel Worksheet Functions | |||
Format cell color based on multiple cell values | Excel Discussion (Misc queries) | |||
Format cell color based on multiple cell values | Excel Discussion (Misc queries) |