Programming cells to read other cells

[Dig]

I have a worksheet that I am setting up. There will be 8 columns and
rows.

What I want to do is setup rows 2 - 5 to look into row 1 for data.

Ex
Row 2A, 3A, 4A, 5A looks in 1A
Row 2B, 3B, 4B, 5B looks in 1B
Etc

So when no data has been typed into cell 1A nothing will appear i
2-5A
But if the Letter A is typed into 1A then 2A shows G 3A shows L, etc

The above is just an illustration but however this is done it will nee
to read letters not numbers.

There is a finite group of letters I am using. The most any cell need
to return is 1 of 12 different letters depending on what is typed int
row1. There may need to be a symbol or two also with certain letters
Ex: V* or E#

I know I can use IF Then but is there a better way to do this?

Thanks
Di

 

[Myrna Larson]

I don't think you *can* use IF, because you can "nest" IF formulas only 7
levels deep. You talk about typing one of 12 letters in A1. IF won't work for
that.

You need to set up a table that has several columns: in the first column, you
put the valid entries for cell A1 (I assume you mean A1. 1A isn't a valid cell
reference); in the columns to the right, you put the information that is to
appear in the cells below.

For example, if you type A into A1, A2 is to show G, A3 is to show L, etc, so
the first row of this table would look like this:

A G L

A is what will be typed in A1; G is what is returned to row 2; L is what is
returned to row 3, etc.

Let's say you put that table in cells K1:R12

The formula for A2 is

=VLOOKUP(A$1,$K$1:$R$12,ROW()-ROW($A$2)+2,0)

This will get what you typed in A1, look for it in K1:K12, and return
information from the corresponding cell in L1:L12.

In A3, the formula is the same. But this will return information from the 3rd
column of the table, M1:M12, because the column number in the table is
determined by the row number of the cell containing the VLOOKUP formula.

BUT.... the above assumes that if you type A anywhere in A1:H1, regardless of
the column, always want to see G in the row below it. If that isn't true, then
you would need 8 tables, one for each column.

IOW, if A means one thing when it's in A1 and a different thing when it's in
D1, you need different tables for the entry in A1 vs the entry in D1.