Results 1 to 10 of 10

20080620, 17:03 #1
 Join Date
 May 2002
 Location
 Smithfield, Free State, South Africa
 Posts
 210
 Thanks
 0
 Thanked 0 Times in 0 Posts
Use tab names in formula (Excel 2003 SP2)
I have a workbook the contains tabs for each month named 01 02 03 04 etc
in my formula I want to take information in a cell from a particular sheet, but without naming it.
In otherwords my formula must refer to one sheet previous to the active, or two sheets depending on the requirement.
Is this possible?
The formula should look like this for example: if(two_sheet_previous_to_active_sheet_cell_ a1="sub",0,two_sheet_previous_to_active_sheet_cell _ a1)
Regards
Lynden

20080620, 17:38 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Use tab names in formula (Excel 2003 SP2)
I'd enter the sheet name in a cell on each sheet. The cell may be hidden. Let's say the sheet name is in cell A50.
If you want to refer to cell A1 on the previous sheet, you can use
<code>
=INDIRECT("'"&TEXT(A501,"00")&"'!A1")
</code>
and if you want to refer to A1 on the sheet for two months back, use
<code>
=INDIRECT("'"&TEXT(A502,"00")&"'!A1")</code>

20080625, 17:06 #3
 Join Date
 May 2002
 Location
 Smithfield, Free State, South Africa
 Posts
 210
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Use tab names in formula (Excel 2003 SP2)
Thank you Hans
It works just the way I want it.
Regards
Lynden

20080625, 17:24 #4
 Join Date
 May 2002
 Location
 Smithfield, Free State, South Africa
 Posts
 210
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Use tab names in formula (Excel 2003 SP2)
Hans
It works fine when I just use your suggestion, but when I add an IF statement it tells me I am using too many arguments!
What am I doing wrong?
=IF(indirect(""&text($t$t2,"00")&"'!AC4"=0,0,(indirect(""&text($t$t2,"00")&"'!AC4")))
What I want to say is that if the cell T2 on the previous sheet is 0 the input 0 otherwise give me the contents of the cell.
Regards
Lynden

20080625, 17:36 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Use tab names in formula (Excel 2003 SP2)
There's a misplaced closing parenthesis and missing single quotes. Also, $t$t is not a valid cell reference. Try
<code>
=IF(INDIRECT("'"&TEXT($T$12,"00")&"'!AC4")=0,0,(INDIRECT("'"&TEXT($T$12,"00")&"'!AC4")))
</code>
This assumes that you meant $T$1 instead of $t$t. However, this formula is equivalent to
<code>
=INDIRECT("'"&TEXT($T$12,"00")&"'!AC4")
</code>
so I don't know why you need to make it so complicated.

20080625, 18:02 #6
 Join Date
 May 2002
 Location
 Smithfield, Free State, South Africa
 Posts
 210
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Use tab names in formula (Excel 2003 SP2)
Hans
I misunderstood the formula, but it does what it should do.
Thanks also for the corrections and for clarifying.
Will use the original one.
Thanks again
Lynden

20080625, 21:47 #7
 Join Date
 May 2002
 Location
 Smithfield, Free State, South Africa
 Posts
 210
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Use tab names in formula (Excel 2003 SP2)
Hi Hans
Lets take this a bit further 
I have a sheet named 07DelRec and want to refer to the previous sheet i.e. 06DelRec and take the value of a cell, say A1 which contains the vale 401 and add 1 to it so that the answer on 07DelRec will be 402
Is this possible?
or does this only work when sheets that have number names?
I tried the formula that you sent to me but it gives me a #VALUE error
Lynden

20080625, 21:57 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Use tab names in formula (Excel 2003 SP2)
Let's say that you enter the number 6 in cell A50 on 06DelRec, and the number 7 in cell A50 on 07DelRec etc.
You can then use the following formula to add 1 to the value of cell A1 on the previous sheet:
<code>
=INDIRECT("'"&TEXT(A501,"00")&"DelRec'!A1")+1
</code>
The same technique as higher up in this thread is used to get the number part of the name of the previous sheet, but now it's concatenated with DelRec.

20080625, 22:14 #9
 Join Date
 May 2002
 Location
 Smithfield, Free State, South Africa
 Posts
 210
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Use tab names in formula (Excel 2003 SP2)
Thank you Hans
Could you please give me a breakdown in plain language as to what each part means.
For example what does the "00" mean or do?

20080625, 22:22 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Use tab names in formula (Excel 2003 SP2)
=INDIRECT("'"&TEXT(A501,"00")&"DelRec'!A1")+1
I have assumed that cell A50 contains a number that corresponds to the sheet name.
For example on sheet 08DelRec, cell A50 would contain 8.
A501 is the number corresponding to the previous sheet  in our example 7.
The TEXT function returns this number with the specified format. "00" means: always return (at least) two digits, inserting a leading zero if necessary.
In this example, TEXT(A501,"00") evaluates to "07".
The expression <code>"'"&TEXT(A501,"00")&"DelRec'!A1"</code> concatenates this with some fixed parts.
This results in <code>"'"&"07"&"DelRec'!A1"</code> which in turn evaluates to <code>"'07DelRec'!A1"</code>
The INDIRECT function looks up the value of the cell with this address, i.e. cell A1 on 07DelRec.
Finally, 1 is added to the result of INDIRECT.