Lookup values in a table, with Row & Column Heading references?

[Coopers]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)

I'm working on a Wages Table with the Column headings being Levels 1 through to 4. The Row Headings are the age brackets between 16 through to 21. Therefore I have 20 variables. I'm looking for a formula that is able to cross reference the Column and Row Heading to a singular variable. I wish to imput the references to the Column and Row heading via a list Box.

example:
List Box 1 - drops down to select "Level 3" (D1)
List Box 2 - drops down to select "Age 20" (A6)

Therefore the answer is "$20.75" (D6)

Thank You

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)

I'm working on a Wages Table with the Column headings being Levels 1 through
to 4. The Row Headings are the age brackets between 16 through to 21.
Therefore I have 20 variables. I'm looking for a formula that is able to cross
reference the Column and Row Heading to a singular variable. I wish to imput
the references to the Column and Row heading via a list Box.

example:
List Box 1 - drops down to select "Level 3" (D1)
List Box 2 - drops down to select "Age 20" (A6)

Therefore the answer is "$20.75" (D6)

Thank You

Name the ranges in your wage table with the columns named: Level_1, Level_2,
etc; and the rows labeled Age_16, Age_17, etc. Then use 2 list boxes, one
for the columns, and one for the rows. You can then locate the correct cell
with an intersection formula. Suppose the column headings are in G5:J5, and
the row headings are in F6:F10

It will get a little messy, as a list box will only use a vertical array.
So, you will have to copy the column headings, and paste-transpose them to
somewhere out of the way. Have the list box use this array. Then if one list
box puts its result in A1 and the other in B1, then the formula:
=indirect(index(G5:J5,A1)) indirect(index(F6:F10,B1))
Will give you the intersection. Note the Space in the formula above, it is
required.

 

[Coopers]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)

I'm working on a Wages Table with the Column headings being Levels 1 through
to 4. The Row Headings are the age brackets between 16 through to 21.
Therefore I have 20 variables. I'm looking for a formula that is able to cross
reference the Column and Row Heading to a singular variable. I wish to imput
the references to the Column and Row heading via a list Box.

example:
List Box 1 - drops down to select "Level 3" (D1)
List Box 2 - drops down to select "Age 20" (A6)

Therefore the answer is "$20.75" (D6)

Thank You

Name the ranges in your wage table with the columns named: Level_1, Level_2,
etc; and the rows labeled Age_16, Age_17, etc. Then use 2 list boxes, one
for the columns, and one for the rows. You can then locate the correct cell
with an intersection formula. Suppose the column headings are in G5:J5, and
the row headings are in F6:F10

It will get a little messy, as a list box will only use a vertical array.
So, you will have to copy the column headings, and paste-transpose them to
somewhere out of the way. Have the list box use this array. Then if one list
box puts its result in A1 and the other in B1, then the formula:
=indirect(index(G5:J5,A1)) indirect(index(F6:F10,B1))
Will give you the intersection. Note the Space in the formula above, it is
required.

--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Bob,[/QUOTE]
Thank you for your reply, unfortunately an error is resultant with the supplied formula.
"A value used in the formula is of the wrong data type" is the message associated with the error.
I have triple checked everything 3 times over to ensure compliance to your criteria ie even having everything in the cells nominated but I'm still coming up with an error.

Am I able to send the small file to view if need be?

Thank you for your assistance in this matter.

Glynn

 

[Bob Greenblatt]

Thank you for your reply, unfortunately an error is resultant with the
supplied formula.
"A value used in the formula is of the wrong data type" is the message
associated with the error.
I have triple checked everything 3 times over to ensure compliance to your
criteria ie even having everything in the cells nominated but I'm still coming
up with an error.

Am I able to send the small file to view if need be?

Thank you for your assistance in this matter.

Glynn

Yes, you can send me the file and I'll take a look.