** automatically filling in rows **
Posted by Robby on February 17, 2001 8:28 AM
OK I'm sure this is a stupid newbie question but I have
tried reading through the posts and the help file for
Excel and I haven't yet found the answer.
Assume I have a worksheet setup with data on a nutritional study.
A B C D
1 Bread 80 12 24
2 Milk 110 45 14
3 Eggs 60 42 12
4 Cheese 40 20 9
5 etc.
6 etc.
These are a few sample rows. Some of these rows
WILL be repeated EXACTLY throughout the worksheet.
What I would like is a way to have some rows
automatically filled in with previous data very much
the way it can autofill based on adjacent cells.
For example in the data above with have Bread in A1
and its corresponding data. If I type Bread in
A24, I would like it to fill out the rest of the row
exactly like it is on the first row.
I know you can do custom lists but I have MANY data
items so adding these lists would get tedious.
Any suggestions? BTW this is Excel 97.
cheers
Posted by Aladin Akyurek on February 17, 2001 9:51 AM
Let's say you have your data in A1:D4.
Type
B5 =IF(A5<>"",VLOOKUP(A5,$A$1:D4,{2,3,4},0),"")
and select B5:D5, then go the formula bar, hit control+shift+enter, and copy the selected cells (B5:D5) as far down as you wish.
If you enter Bread in A5, you will get the values associated with Bread in B5:D5. If you enter a new item say in A6 (Garlic will do), just type the values associated with this item in B6:E6 (overwriting the formulas and of course #N/A values).
Hope you find this machinery good enough to do the job.
Aladin
Posted by Robby on February 17, 2001 9:28 PM
Thanks Aladin for the post. That didn't seem to
work for me but I found another way which isn't
exactly as easy as I had liked but it will do.
Here are the steps I found.
1. Open new worksheet and select as many cells
as I expect to have custom fill lists
2. Click Format -> Cells
3. Select Text and OK
4. Enter the all the rows which I KNOW will be
repeated first.
5. After I have all these rows, select them all
and go to Tools -> Options -> Custom Lists
and then click Import
It worked for me.
cheers
validus
Posted by Aladin Akyurek on February 18, 2001 1:32 AM
Found a way, that's just fine.
Concerning the formula-based solution I suggested,
I seem to have overlooked an array-issue.
But if you change the formula to
B5 =IF($A5<>"",VLOOKUP($A5,$A$1:$D4,column(),0),"")
then you need to copy this to column C and D, then down as far as needed.
Aladin
