Forum Discussion

DumDeeDah's avatar
DumDeeDah
Copper Contributor
Feb 11, 2022

Need a formula advances sheet name when copied

I want to carry forward 1 figure on a sheet to the next fortnight sheet. When I copy the sheet to create the next sheet it keeps the same refernce link.

I use ='2022 2 2'!L33

 

This brings in the figure from previous sheet. But then each time sheet is copied it will only obtain the total from the sheet '2022 2 2'

 

How can I have it add on to the next sheet to be '2022 2 16' etc

I hope this makes sense.

Thanks

excel
Formulas and Functions

3 Replies

  • srdobrais's avatar
    srdobrais
    Brass Contributor
    Feb 11, 2022

    DumDeeDah 

    Hello:
    Excel treats sheet names as aliases, that is, sheet names start from Sheet1 to Sheet#? and not like the name displayed in the tab.
    The formula to use would be (in your case):
    '=ADDRESS(ROW(L33); COLUMN(L33);4;1;" Sheet"&SHEET(L33))'
    being:
    'SHEET()' gives you the sheet number in which the selected cell is located.
    'COLUMN()' gives you the column number of the selected cell.
    'ROW()' gives you the row number of the selected cell.
    and
    'ADDRESS()': creates the address for the cell
    See help functions in:
    https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188?ui=es-es&rs=es-es&ad=es#bm19

    • srdobrais's avatar
      srdobrais
      Brass Contributor
      Feb 11, 2022
      Give an example of using your formula.
      To see if I can help you.
    • DumDeeDah's avatar
      DumDeeDah
      Copper Contributor
      Feb 11, 2022
      Thanks for responding.

      When I add this formula in I know get a #VALUE! message in the cell below not being able to use this data.

      Plus now the colomns in L wont total on any spreadsheet.

      Any other suggestions.

      Thanks so much.

Resources