Using cell reference to populate reference to another sheet

[stockcontroller]

I am using a workbook with multiple sheets - there are certain cells in which I would like to reference a cell in the previous sheet.

New sheets are created on a weekly basis, and the new sheet's references should always refer to the previous sheet - the sheet names are sequential as I thought this would make it easy (1,2,3,4,5)

I have a cell (BI) which I can manually input with the name of the previous sheet = i.e. '14' but this is about as much work as I want users to do - if that.

I thought of using this cell to populate references to the previous sheet. To minimise work when new sheets are created.

I need to use this right now in the following if/or function;

=IF(OR('13'!AJ3>0,Q3>0,R3>0,T3<(2*X3)),T3,"NO")

(this works)

I have attempted to replace the reference in bold as follows but it hasn't worked;

=IF(OR("'"&$B$I&"'!AJ3">0,Q3>0,R3>0,T3<(2*X3)),T3,"NO")

...in the sense that it never returns a negative. In addition the AJ3 doesn't change to AJ4 when I drag it down so it is essentially useless

I also tried indirect reference with no luck


This might have been the wrong tree to bark up, my excel knowledge is pretty narrow. Essentially I just want a way for sheet B to reference cells in sheet A, and to be able to copy sheet B to create sheet C and have those references now refer to sheet B with minimal fuss.

Thank you

 

[dave3009]

Hi

I think the problem you are facing is that BI is not a cell reference, so $B$I wouldn't work. Is it actually B1, in which case the function you would be looking for is =INDIRECT($B$1&"!AJ3").

HTH

Dave

 

[svendiamond]

What is BI? Do you mean B1? $B$I is not a valid cell reference... ?

 

[stockcontroller]

Whoops - hang on let's see if I did that!

 

[stockcontroller]

The reference should have been $BI$1 - however this didn't fix it.

I have found a solution however...using CONCATENATE I created a list of sheet/cell references in column BI, and referenced this in the following formula;

=IF(OR((INDIRECT(BI3,TRUE)>0),Q3>0,R3>0,T3<(2*X3)),T3,"NO")