Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have three worksheets at the bottom.

Assumptions - where info is filled out
Results 1 - depending on the model, this results tab may be used
Results 2 - depending on the model, this results tab may be used

In the Assumptions tab, I want to reference cells in either the Results 1 tab or Results 2 tab, depending on which model is being used. How do I use an IF or VLOOKUP or indirect function to do this?

This is my formula, which works great unless I delete Results 1 in which case, I want it to look at the same cell in Results 2: =(-1*0.26*($D$2*'results 1'!$C$3*$D$5*$D$53*(1-$D$4)))*0.5

share|improve this question
add comment

2 Answers 2

How do you determine which model is used? If that can be described with logic, then you can build an IF statement with it. For example, if A1 contains the number of the model, 1 or 2, then you could do something like

=(-1*0.26*($D$2*if(a1=1,'results 1'!$C$3,'results 2'!$C$3)*$D$5*$D$53*(1-$D$4)))*0.5

Or you could use the INDIRECT() function to refer to a cell that has the worksheet name you want to use

share|improve this answer
add comment

If I understood your needs correctly you can achieve this in two ways:

  1. =(-1*0.26*($D$2*IFERROR('results 1'!$C$3,'results 2'!$C$3)*$D$5*$D$53*(1-$D$4)))*0.5
  2. =(-1*0.26*($D$2*IFERROR(INDIRECT("'results 1'!$C$3"),INDIRECT("'results 2'!$C$3"))*$D$5*$D$53*(1-$D$4)))*0.5

1st one is shorter, but once you delete 'results 1' tab, you will lost reference to your cell on 'results 1' (the formula will still work taking value from 'results 2' tab) and even if you create 'results 1' tab again it will not recognize it.

While 2nd one should pick up value from 'results 1' tab as soon as you recreate it after delete. On the cons side however, you will have to maintain cell address manually if you ever move your cell location or add/delete extra colums/rows around it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.