Checking multiple tables, then 2 axes to display a number.

[Bwest2013]

Hey all!

I'm trying to make an excel sheet for my ROTC Battalion's APFT scores. My goal is to make a table to autofill the scores when the number of repetitions has been entered. I have all of the standards in tables on 2 other sheets, one for males one for females.

Thus, the rows look like this : Name, Age, Gender, # of pushups, pushup scores, # of situps, situp score, run time, run score, total. The non-editable columns of scores will have their values pulled from the table.
APFT.xlsx
The issue that I'm having is that the individuals first have to be classified as male or female. If male, one set of scores needs to be used. If female, the other. From there, I tried using MATCH to analyze the table corresponding to the type of work out and compare the age grouping with the number of repetitions... But it isn't working. Vlookup isn't working either because apparently it can only analyze one variable at a time.

So distinguish male/female. Reference corresponding table, match age into age group and match repetition number on table to display the score in the score column.

Attached is the excel sheet.

Thanks for any help!
Brittanie

[DBY]

Hello Brittanie
Not sure if you're still looking for help on this problem, if so take a look at the attached reply. It uses a series of Named Ranges with IF and VLOOKUP to hopefully return the values you're looking for. You do need to be consistent with your layout, as in your example, for the formulas to work.

Hope this helps.
DBY

[Bwest2013]

DBY,

Thanks for your response. For some reason I can't download it; it says I don't have the privileges to. I just joined the site yesterday, is there some sort of approval process that I have to go through first?

Can't wait to see it, there's too much nesting for my newbie brain to figure out on its own.

[DBY]

As far as I am aware, you only need to be logged-in to download attachments. It sounds as if you don't have user privileges on the computer you are using. The type of user account you have can sometimes restrict what you're allowed to do. If you have administrator rights on your computer then I'm not sure why it prevents the download. Does this happen on all sites or just this one?

[Bwest2013]

Oh, no. It brings me to the log in screen, I log in, it says nice to see you, but continuously redirects to the log in screen. Since all my stuff is saved for log in, none of it is incorrect... the only other option on the screen is that I don't have permissions from the site. Maybe it was just a fluke, I'll try again.

Edit: Yeah, it's still saying the same thing.

[DBY]

Sorry I don't have the solution to this. Perhaps you can contact one of the Moderators or Admin. See the FAQ's.

*Just to update. I've posted a request in the Water Cooler (Call in the Cavalry) Forum for suggestions and the Tech Team are looking in to your problem.

http://www.excelforum.com/the-water-...ml#post3432522