Putting two formulas in one cell

[boakley]

Hi everyone!

Looking for some help if possible.
I have cell G4 that needs to read data from one of two sheets, depending on the data that is entered for cell D4 (either male or female). The next two cells, E4 (age) and F4 (number of repititions performed) will be manually entered to get the outcome for G4. I just need to know how to put both of these formulas into G4 so that if I input “M” into cell D4, cell G4 will read from the male sheet and vice versa. Thank you in advance for your help!!!

The formulas that I need to put in G4 are:

=IF(D4=“M”(INDEX(MALE_PU!B2:AO117,0),MATCH(MALE_PU!A1:A145,0),MATCH(MALE_PU!B1:AO1,0)))

and the second formula is:

=IF(D4=“F”(INDEX(FEMALE_PU!B2:AO117,0),MATCH(FEMALE_PU!A1:A145,0),MATCH(FEMALE_PU!B1:AO1,0)))

 

[PaddyD]

General approaches:

1) use an indirect() to conditionally construct the tab reference within the formula
2) use something like choose() or another if() to pick which formula to execute

...but before we get there we need to address the fact that your index() match() constructions aren't working (indexing row 0 won't get you far!)

Can you describe what you're trying to do with these formulas - a worked example would help.

 

[boakley]

@PaddyD Hi,

Sorry about that. I totally messed that formula up. The correct formula is:

=IF(D4="M",INDEX(MALE_PU!B45:AO117,MATCH(F4,MALE_PU!A45:A117,0),MATCH(E4,MALE_PU!B1:AO1,0)))

The second formula is the same except for the "Male" and "M" would be "Female" and "F" as well as instead of using Sheet 1, it would be using Sheet 2.

I'm not sure why but I am unable to upload the picture of my table that I am building.

I have a few other things that I am trying to do with this as well but my biggest issue is that I cannot seem to figure out how to get both of the formulas into this cell so that it will pull the correct data from the correct place.

So what I am trying to do:
I am a Drill Sergeant and I need to make this table so that we can track how the Privates' Physical Training scores are doing. Each Private has their own row that has their information in it (D4, D5, D6... E4, E5, E6...). Column D is to choose either "Male" or "Female". Column E is to manually input their age. Column F is to manually input the number of repititions performed of the excercise they were doing. I have made different sheets that correspond with the age numbers across the top row and repititions going down the first column. Everything else is the score associated with that age and repititions performed.

The problem is that since male and female physical fitness standards are different, I have to enter their gender into the mix. So essentially, I would like to manually input the gender, which would tell the program which "SHEET" the data is going to come from. Then I would manually input their age and repititions performed so that cell G would populate with their correct score. The formula works but I cannot enter both of them for some reason. I know I am missing something. Also, when I entered the formula into the cell and then hit enter, "FALSE" appeared in the cell. When I entered the gender, the "FALSE" turned to "#N/A". When I entered the age and repititions, the correct score came up like I wanted. I do want to get rid of those errors so that it is blank before starting. I hope this helps. Thank you for your help by the way.

Example of the Table[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]

NAME​

[/TD]
[TD]

GENDER​

[/TD]
[TD]

AGE​

[/TD]
[TD]

PUSH UP REPS​

[/TD]
[TD]

PUSH UP SCORE​

[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]

xxxx​

[/TD]
[TD]

M​

[/TD]
[TD]

31​

[/TD]
[TD]

77​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]

xxxx​

[/TD]
[TD]

F​

[/TD]
[TD]

31​

[/TD]
[TD]

50​

[/TD]
[TD]

100​

[/TD]
[/TR]
</tbody>[/TABLE]


Example of Female Score Sheet[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]

AGE​

[/TD]
[TD]

29​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

32​

[/TD]
[TD]

33​

[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]

REPS​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]

50​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

105​

[/TD]
[TD]

105​

[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]

49​

[/TD]
[TD]

99​

[/TD]
[TD]

99​

[/TD]
[TD]

99​

[/TD]
[TD]

104​

[/TD]
[TD]

104​

[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]

48​

[/TD]
[TD]

98​

[/TD]
[TD]

98​

[/TD]
[TD]

98​

[/TD]
[TD]

103​

[/TD]
[TD]

103​

[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]

47​

[/TD]
[TD]

96​

[/TD]
[TD]

96​

[/TD]
[TD]

96​

[/TD]
[TD]

102​

[/TD]
[TD]

102​

[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]

46​

[/TD]
[TD]

95​

[/TD]
[TD]

95​

[/TD]
[TD]

95​

[/TD]
[TD]

101​

[/TD]
[TD]

101​

[/TD]
[/TR]
</tbody>[/TABLE]



Example of the Male Score Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]

AGE​

[/TD]
[TD]

29​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

32​

[/TD]
[TD]

33​

[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]

REPS​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]

77​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

102​

[/TD]
[TD]

102​

[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]

76​

[/TD]
[TD]

99​

[/TD]
[TD]

99​

[/TD]
[TD]

99​

[/TD]
[TD]

101​

[/TD]
[TD]

101​

[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]

75​

[/TD]
[TD]

98​

[/TD]
[TD]

98​

[/TD]
[TD]

98​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]

74​

[/TD]
[TD]

97​

[/TD]
[TD]

97​

[/TD]
[TD]

97​

[/TD]
[TD]

99​

[/TD]
[TD]

99​

[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]

73​

[/TD]
[TD]

96​

[/TD]
[TD]

96​

[/TD]
[TD]

96​

[/TD]
[TD]

98​

[/TD]
[TD]

98​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[PaddyD]

Right then...

Given the number of times you're referencing each sheet in each formula I would stay away from option (1) above.

Assuming you've comfortable with the index(match( bits, isn't it just:

=IF(D4="M",INDEX(MALE_PU!B45:AO117,MATCH(F4,MALE_PU!A45:A117,0),MATCH(E4,MALE_PU!B1:AO1,0)),IF(D4="F",INDEX(FEMALE_PU!B45:AO117,MATCH(F4,FEMALE_PU!A45:A117,0),MATCH(E4,FEMALE_PU!B1:AO1,0)),""))

...which basically just wraps your core formulas in a single if()..?

 

[boakley]

@PaddyD

That worked perfect!!! Thank you so much for that. If it is ok, can I ask you one more thing? I have a conditrional format on the score cells. If the score is >49, the cell turns green and if the score is <50, the cell turns red. If there is no value at all in the score cells, then the score cells stay white. Ever since I input the formulas that you provided, the cell stays green until there is a value that is <50 and only then it turns red. Any idea's? I would like them to stay white until a value is entered.