Use ampersand to change year in formula
Results 1 to 3 of 3

Thread: Use ampersand to change year in formula

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    5,332
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Use ampersand to change year in formula

    I have the following formula


    Code:
     =INDEX('[Sales Report2019.xlsm]Sales by month'!$A$11:$N$11,MATCH('[Sales Report2019.xlsm]Sales by month'!B1,'[Sales Report2019.xlsm]Sales by month'!$A$1:$M$1))

    I have the year in Q1


    I would like to use an ampersand to combine the year in the formula with the year , but I cannot get it to work foe e.g Sales Report"&Q1&".xlsm


    It would be appreciated if someone could kindly assist me

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,519
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Use ampersand to change year in formula

    When you do that it turns into text and is no longer a cell reference look at the indirect function.

    Note that indirect does not like closed workbooks so Sales report2019 will need to be open when this workbook is opened

    https://support.office.com/en-us/art...1-92b6306fa261
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular
    Join Date
    Jun 2006
    Posts
    5,332
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use ampersand to change year in formula

    Thanks for the link

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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