Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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
| Check out our Excel Resources | ||||
 |
 |
|||
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
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
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