none
how to reference a sheet using a range name as part of the name RRS feed

  • Question

  • I have a workbook that has a worksheet called "Budget 2018" I refer to this as ='Budget 2018'!F5 and it works fine. I have a range name "year" which for this year is 2018, but in future it will be 2019 2020 along with new worksheets by those names. So I want to create a reference using the range name, something like ='Budget ' &year!F5 or ='Budget &year'!F5. These don't work. any help would be greatly appreciated. Don't know if I am doing something wrong in the syntax or what. 

    Thank you

    rioverdej


    • Edited by rioverdej Wednesday, November 7, 2018 9:23 PM
    Wednesday, November 7, 2018 9:22 PM

Answers

  • Hi Yuki, 

    Thank you for your help, But the link you sent me didn't quite answer the question. That's ok because I found a work around that works using the find and replace method. This only works because of the way my spread sheet is.

    Again Thank you

    rioverdej

    • Marked as answer by rioverdej Thursday, November 8, 2018 8:42 PM
    Thursday, November 8, 2018 8:41 PM

All replies

  • Hi rioverdej,

    Do you want to use formulas to do that?

    For your information:

    Dynamic worksheet reference

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 8, 2018 2:27 AM
    Moderator
  • Hi Yuki,

    Yes I want the formula ='Budget 2018'!F5 to be like ='Budget &year'!F5, with year being a range name representing the year

    Thank you for responding

    rioverdej

    Thursday, November 8, 2018 4:03 AM
  • Hi rioverdej,

    You can write the following formula in a cell to get the name of current year:

    =YEAR(TODAY())

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 8, 2018 6:16 AM
    Moderator
  • Hi Yuki, 

    Thank you for your help, But the link you sent me didn't quite answer the question. That's ok because I found a work around that works using the find and replace method. This only works because of the way my spread sheet is.

    Again Thank you

    rioverdej

    • Marked as answer by rioverdej Thursday, November 8, 2018 8:42 PM
    Thursday, November 8, 2018 8:41 PM