How do I auto populate rows based on one cell value.

[tbucki1]

I need to auto fill 10 rows in Excel from a database based on one cell value. How do I do this? For example in Cell A1 I have the value "1993" and in A2 through A12 below I have empty cells. On a separate tab I have my list of data (See sample data below). In column A, I have a list of all the Cars, and in Column B I have the year. For example:

Car1 1993
Car2 1985
Car3 1955
Car4 2001
Car5 1993
Car6 1997
Car7 1999
Car8 1993
Car9 1967
Car10 1985

When I type in "1993" in Call A1 I want Car1, Car5, and Car8 to appear in the cells below. When I type "2012" in A1...Nothing pops up, because there are no cars with that year. When I type "2001" only Car4 pops up.

Without using VBA how can I build this so that the list is automatically updated each time I change the value of Cell A1? A2 through A12 should be dynamically populated based on A1 value.

Thanks!

 

[tbucki1]

Hi Rick,

Will this Array equation not work if the value in A1 is an equation? Instead of typing in "1993" or "All", I have the the following equation in Cell A1, "=RIGHT(LEFT(C6,7),4)" that produces the value of "1993" that you see in the cell. So when you click/mouse over cell A1 you see an equation, not a hard coded value like "1993" or "2001".

When I type in "1993" into cell A1 the array works, but when I try and add an equation into A1 that pulls in a value of "1993" the array doesn't seem to work. Anyway to make this work with an equation in A1?

Thanks!

 

[Rick Rothstein]

Will this Array equation not work if the value in A1 is an equation? Instead of typing in "1993" or "All", I have the the following equation in Cell A1, "=RIGHT(LEFT(C6,7),4)" that produces the value of "1993" that you see in the cell.

Yes, the array will work, but it needs a small modification (shown in red)...

=IFERROR(INDEX(Sheet2!A$1:A$10,SMALL(IF((A$1="All")+(Sheet2!B$1:B$10=0+A$1),ROW(A$1:A$10)),ROWS(A$1:A1))),"")

The reason is that when you type 1993 into a cell that is formatted as "General", it becomes a number like what is in cells B1:B10 in your list on Sheet2; however, the return value from your formula is a text string that looks like a number, not real number, so the test for equality in the formula fails (numbers do not equal text even if they look the same). The addition of the zero that I show in red above forces Excel to convert text that looks like a number to a real number so that the equality test can be performed.

 

[revolvermac]

Hey all,

I hate to necro an old thread but I've been doing a lot of experimentation with Rick's ARRAY formula and I'm hitting a bit of a snag. I admittingly know very little about how the function works; but when working with an index of 60 rows as opposed to the OP's 10, the formula no longer works. In addition, the row whereby the data should be populated is significant further down on the sheet, at row A33.

Really, I guess I'm just trying to understand how this formula works so I can modify it to meet my needs as they vary from the OP's. Other answers around the net all suggest VBA but if I can avoid that I'd sure like to.

 

[vickacct30]

I will like to do the same thing but neither the VBA code or the index array formulae are working...help

 

[Rick Rothstein]

I will like to do the same thing but neither the VBA code or the index array formulae are working...help

I think we need a little bit more information. What is your layout? What are you trying to do with it? In what way is the VBA code or Index array formula not working for you (what is or isn't happening)?