# Thread: Macro To change Month in Link

1. ## 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. 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. Hi Steve

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

Howard

4. 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. Hi Steve

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

Howard

7. 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. 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. 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. 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
•