Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro To change Month in Link

    I have have several sheets ending with the name JNL. I would like a macro that will prompt and allow me to change the month. The formula contain links in Cols A for eg

    =IF('Sep-2013'!H164>0,('Sep-2013'!H164),"")

    I would like the month in this case to cahnge SEP-2013 to Oct-2013

    Your asistance in this regard is most appreciated

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Why the need to create a macro? Why not use the built-in find and replace?

    If this is a routine operation why not base the formula on a cell (eg A1), with something like:
    =IF(indirect("'" & $A$1 &"'!H" & row())>0,indirect("'" & $A$1 &"'!H" & row()),"")

    A1 could even be a formula based on the date:
    =text(today(),"mmm-yyyy")

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for your valuable input. I will try this. Please explain to me how the formula indirect & Row works

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Row() gives the row number of the cell the formula is in
    Indirect converts text to a reference.
    Assume A1 has the text "Oct-2013" and you have the formula in A164:
    =IF(indirect("'" & $A$1 &"'!H" & row())>0,indirect("'" & $A$1 &"'!H" & row()),"")

    Then:
    ("'" & $A$1 &"'!H" & row())
    will equal the text:
    "'Oct-2013'!H164"
    Now this looks like a cell reference but is not. If you do a compare with that you compare that text rather than the value in the cell. What you want is the cell reference to that sheet:
    'Oct-2013'!H164

    Which you get with the indirect function. Thus you go from in A164:
    =IF(indirect("'" & $A$1 &"'!H" & row())>0,indirect("'" & $A$1 &"'!H" & row()),"")
    to
    =IF(indirect("'Oct-2013'!H164")>0,indirect("'Oct-2013'!H164","")
    to what is interpreted as:
    =IF('Oct-2013'!H164>0,'Oct-2013'!H164,"")

    And when the formula is copied down the row, it adjusts automatically...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2013-10-23)

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the explanation in laymans terms. I fully uderstand how this works now

    Howard

  7. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    I have the month and year on sheet "Months" for eg Sept-2013 and the value pertaining to Sep-2013 is on sheet Sept-2013 cell G86 -see formula =IF('Sep-2013'!G86>0,('Sep-2013'!G86),"")

    Kindy amend your Formula =IF(indirect("'" & $A$1 &"'!H" & row())>0,indirect("'" & $A$1 &"'!H" & row()),"") to accomodate this

    Howard

  8. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    I got the formula to work =IF(INDIRECT("'"&Months!$A$16&"'!G86")>0,(INDIRECT ("'"&Months!$A$16&"'!G86")),"")

    However, when I copy the fomula to the next Cell I want G86 to change to G87. It would be appreciated if you could amend this so that the row number changes to 87 when I copy gown and the column chage to H when I copy to the next column for eg =IF(INDIRECT("'"&Months!$A$16&"'!G87")>0,(INDIRECT ("'"&Months!$A$16&"'!G87")),"")

    =IF(INDIRECT("'"&Months!$A$16&"'!H86")>0,(INDIRECT ("'"&Months!$A$16&"'!H86")),"")

    Your assistance in this regard is most appreciated

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Howard

    This should work..
    =IF(INDIRECT("'"&Months!$A$16&"'!G" &ROW(G86))>0,(INDIRECT("'"&Months!$A$16&"'!G" &ROW(G86))),"")

    If you copy this formula down, you will get G86 changing to G87 etc etc.

    zeddy

  10. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-10-28)

  11. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for help. Formula works perfectly

    Howard

Posting Permissions

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