• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Updating Link Based on MONTH AND YEAR

D

Dale Thompson

New Member
Hey Guys,

I have a spreadhseet that we use at work to automatically process current sales data and gives a score based on that data, the data is pulled from an external sales report.

I use the "VLOOKUP" to acertain the data used for each section.
The report changes its value based on the year and month.

EG of the FORMULA -
=IF(A8=ISBLANK(""),"",VLOOKUP(A8,LINK TO EXTERNAL BOOK [1311 Sales Partners Nov (I).xlsm]Team Performance'!$F$5:$DT$808,6,FALSE))

The Report for Dec Will be [1312 Sales Partners Dec (I).xlsm

I have manged to create a cell that will give me the correct link every month.
It automaticaly updates, but I cant seem to get the text itself or even a cellrefernce to work within the vlook up formula.
I want the highlighted section to update with the month so I dont have to ctrl+F it every month.
=IF(A8=ISBLANK(""),"",VLOOKUP(A8,LINK TO EXTERNAL BOOK [1311 Sales Partners Nov (I).xlsm]Team Performance'!$F$5:$DT$808,6,FALSE))
 
Last edited:
Hi Dale,

you can try the INDIRECT function:

LINK TO EXTERNAL BOOK [1311 Sales Partners Nov (I).xlsm]Team Performance'!$F$5:$DT$808

Place the above in a cell say A1

and then for the VLOOKUP enter as follows

VLOOKUP(A8,INDIRECT(A1),6,FALSE)

You can then change the value in A1 to suit

cheers

kanti
 
Hey Kchiba,

Thanks for your reply, I have tried the indirect() formula and it comes up with #ref
The cell A1 = would be text data would that matter ?
 
One word of caution!

INDIRECT doesn't work with closed workbooks. You will have to keep second workbook open otherwise you will get #REF error when the formula gets recalculated with second workbook closed.
 
hey guys, thanks for all the warnings, I can get the indirect() function to work on any new workbook, the minute I change the current formulas to reference the worksheet with the data it just comes up with a #ref, mmm ill have a look at the pull function or even try to find some alternative for indirect().

Thanks for all your help guys very much appreciated.
 
Hi, Dale Thompson!
Glad we helped you understanding how it works, even if it's unsolvable directly unless you implement something like HG's pull function. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top