Need lookup help

  • Thread starter Thread starter Rob Jenkins
  • Start date Start date
R

Rob Jenkins

Hi,

I've been working on this sheet off and on for about 3 years and I think I'm
about done with it. However I have one lookup that I can't seem to figure
out.

To help see what I'm trying to explain, you can download the file at
http://armybytes.com/cgi-bin/download.pl?file=1

Basically, I need the field titled 'Screening Table Weight' in both sheets 2
(DA Form 5500-R, cell D20) and 3 (DA Form 5501-R, cell I21) to fill
themselves based on the person's height and age from the first sheet
(Screening Table Weight)

For instance, based on the DA Form 5500-R, the individual is 21 yrs old and
69 inches tall. Going to the Screening Table Weight sheet, you find that
they are allowed 179 lbs. I would much rather have the data pulled from
that sheet instead of the user having to reference the table, not only
because it is quicker, but should be more foolproof if the sheet does it
automatically.

The 5500-R is for males, and the 5501-R is for females, so they will need to
reference different sides of the Screening Table Weight sheet.

I appreciate any help you can give me.

Thank you,
John R. Jenkins
http://armybytes.com
 
Hi Rob
to make this work, you have to slightly chnage the lookup table
'Screening Table Weight'. In row 5 change the heading from 17-20 for
example to 17. That is only enter the starting age in this cell!. The
use the following formula in D20 (for males):
=INDEX('Screening Table Weight'!$A$5:$E$28,MATCH(A7,'Screening Table
Weight'!$A$5:$A$28,0),MATCH(H7,'Screening Table Weight'!$A$5:$E$5,1))
 
Thank you for the last help I received here.

I can't find the message right now, but I was told to put the following in
cell D20 of the first sheet (DA 5500-R)-
=INDEX('Screening Table Weight'!$A$5:$E$28,MATCH(ROUND(A7, 0),'Screening
Table Weight'!$A$5:$A$28,0),MATCH(H7,'Screening Table Weight'!$A$5:$E$5,1))

I have tried using the same (altered for different portion of Screening
Table Weight table) formula for cell I21 of the DA 55001-R, but it won't
work out. Here's what I tried to put in that cell -
=INDEX('Screening Table Weight'!$F$5:$J$28,MATCH(ROUND(A7, 0),'Screening
Table Weight'!$F$5:$J$28,0),MATCH(H7,'Screening Table Weight'!$F$5:$J$5,1))

I'd appreciate any help I can get. I've posted the version I'm working on
at
http://armybytes.com/downloads/bodyfattest.xls

Thank you,
Rob
 
Back
Top