Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .