Copy values from a cell based on values of another cell

[Spence10169]

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="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?

 

[JBeaucaire]

Try this:

=SUMIF(D,"Y",B:B)

 

[JBeaucaire]

Or rather:

=SUMIF(Sheet1!D,"Y",Sheet1!B:B)

 

[Spence10169]

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]

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