Multiple formulas in 1 cell

davecoppins

New Member
Joined
Nov 13, 2018
Messages
11
Hope someone can help

I am trying to change the calculation returned in 1 cell depending on the entry of "X" in another

If an "X" is entered in I2 run the calculation in Worksheet1 - =IF(I$2="x",LOOKUP(AY5,'1'!A$3:B$65)+1)*AY5
If an "X" is entered in J2 run the calculation in Worksheet2 - =IF(J$2="x",LOOKUP(AY5,'2'!A$3:B$65)+1)*AY5
If an "X" is entered in K2 run the calculation in Worksheet3 - =IF(K$2="x",LOOKUP(AY5,'3'!A$3:B$65)+1)*AY5

The formula I am trying to use is =IF(I$2="x",LOOKUP(AY5,'1'!A$3:B$65)+1)*AY5 which works ok as it is but I can't figure out how to get the change to happen when putting "X" in J2/J3

123
X"X""X"
RRPGP £GP %
=IF(I$2="x",LOOKUP(AY5,'1'!A$3:B$65)+1)*AY5

<tbody>
</tbody>









Thanks in advance

David
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Wrong way round

Maybe
=LOOKUP(AY5,INDIRECT(IF(I$2="X","'1'",IF(K$2="X","'2'","'3'"))&"!A$3:B$65")+1)*AY5
 
Upvote 0
Wrong way round

Maybe
=LOOKUP(AY5,INDIRECT(IF(I$2="X","'1'",IF(K$2="X","'2'","'3'"))&"!A$3:B$65")+1)*AY5

Thanks for coming back with this

Entering the "X" in I2 and K2 changes the calculation as required but not in J2 and I can't seem to get the additional J2 formula to work

Thanks in advance

David
 
Upvote 0
Oops, typo!
Should be

=LOOKUP(AY5,INDIRECT(IF(I$2="X","'1'",rIF(J$2="X","'2'","'3'"))&"!A$3:B$65")+1)*AY5

So if I2 is X then return '1'
otherwise
IF J2 is X then return '2'
otherwise return 3 (K2 is X is assumed since I2=X and J2=X both failed if you get this far)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top