Automatically fill in cells?

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I want to create a sheet that has a word in the first cell
and a series of numbers in subsequent cells. For example:

a1- "paper"
a2 - 3
a3 - 5

b1 - "pens"
b2 - 6
b3 - 9

When I type the word "paper" in say, cell f1 I want f2 and
f3 to automatically insert "6" and "9". Is there a way to
do this? This answer may be here but I only know enough
Excel to be dangerous and I'm not familiar with a lot of
the descriptions of cell functions.

Thanks.
 
I don't really know if I understand exactly what you're looking for, but
does this work for you?

In A2 enter:
=(A1="paper")*3+(A1="pens")*6
In A3 enter:
=(A1="paper")*5+(A1="pens")*9

Now, select *both* A2 and A3, and drag across the columns to copy as needed.
 
This helps a little. What I'm actually doing is trying to
set up a worksheet that counts carbs, fats, sugars, etc.
for certain foods and then calculates the daily total. If
I have a hundred foods the strings would be very long for
the A2 and A3 cells. But maybe that's the best way to do
it?
 
Also to clarify. I need to be able to enter any of the
foods in any of the A cells and have it fill in the
numbers in those rows. If the formula specifically refers
to A1 then it wont work if I enter info into A2. I need a
column of info that I enter and the rows need to pick up
the numbers.
Thanks,
 
Mary, search the topics "HLOOKUP Worksheet Function" and "VLOOKUP Worksheet
Function" in Excel help. Depending on how you set up your worksheet, one or
the other of those functions will serve your purpose. Either way, you'll
need to set up a reference table of foods and the carbs, fats, sugars, etc.
that go with them. You'll enter a food in your worksheet, and the lookup
formulas in the cells below (or, better, to the right) will return the
corresponding values from the reference table. Post back if this seems to be
what you're looking for and you have questions.

Now one more thing. Not having seen your data, and knowing little about the
scope of all this, I'll be so bold as to say that you should think about
entering the foods in a column and letting Excel populate the rows, rather
than doing it the way you do in your initial example. If, as you say, you
have a hundred foods but only a dozen or so figures for each of them, you'll
find it easier to create a reference table if you put the food names in a
column (Column A, for example) than in a row. Just a suggestion.
 
In the options there is a tab called Custom lists. If you add the list
"paper, 3, 5" and then write "paper" in A1, you can by "dragging/expanding"
from A1 to A2 and A3, make them filled with "3" and "5".
It's not exactly what you asked for, but perhaps it is useful.

Cheers,
Emil
 
Back
Top