Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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(A50-1,"00")&"'!A1")
    </code>
    and if you want to refer to A1 on the sheet for two months back, use
    <code>
    =INDIRECT("'"&TEXT(A50-2,"00")&"'!A1")</code>

  3. #3
    3 Star Lounger
    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

  4. #4
    3 Star Lounger
    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$t-2,"00")&"'!AC4"=0,0,(indirect(""&text($t$t-2,"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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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$1-2,"00")&"'!AC4")=0,0,(INDIRECT("'"&TEXT($T$1-2,"00")&"'!AC4")))
    </code>
    This assumes that you meant $T$1 instead of $t$t. However, this formula is equivalent to
    <code>
    =INDIRECT("'"&TEXT($T$1-2,"00")&"'!AC4")
    </code>
    so I don't know why you need to make it so complicated.

  6. #6
    3 Star Lounger
    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

  7. #7
    3 Star Lounger
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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(A50-1,"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.

  9. #9
    3 Star Lounger
    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?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use tab names in formula (Excel 2003 SP2)

    =INDIRECT("'"&TEXT(A50-1,"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.
    A50-1 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(A50-1,"00") evaluates to "07".
    The expression <code>"'"&TEXT(A50-1,"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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •