Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course
Date Reference
Hi
I have a formula within a cell that basically copies the price from another spreadsheet. However, the name of the spreadsheet (from which the price is copied from) will change for each month.
Formula within spreadsheet A:
='[INLAND PRICE DIFFS COPY.xlsx]June 15'!$D$8
I want it to automatically choose the current month, or if I was to put the current month ie August within a cell in Spreadsheet A, it knows to pick August 15 spreadsheet instead of June 15 based off that cell.
Your help would be much appreciated!!
RE: Date Reference
Hi Adrian
Yes it is possible and no VBA needed!
Step 1 - Open both your INLAND PRICE DIFFS COPY workbook and the second workbook say Book1.
Step 2 - In Book1 type the month name in a cell say cell A1.
Step 3 - Instead of your link formula type the following
="'[INLAND PRICE DIFFS COPY.xlsx]" & A1 &" 15'!$D$8"
This will display the link formula as text, including the month name typed in A1. (Check that if you change A1 to AUGUST it will change the formula).
Step 4 - Finally add the INDIRECT function. This displays the value referred to by the text reference.
=INDIRECT("'[INLAND PRICE DIFFS COPY.xlsx]" & A1 &" 15'!$D$8")
Hope this does it.
Regards
Doug Dunn
BEST STL
RE: Date Reference
Hi Doug
Many thanks for this - So just to be clear, this will be if the the Workbook "Inland Price Diffs Copy" changes name each month ie "Inland Price Diffs Copy July 2015", "Inland Price Diffs Copy August 2015", etc. What about if the tab name differs ie July 15, August 15 etc?
Essentially these 2 books look at each other for data, 1 has a new tab for each month, the other is a new workbook for the month in question.
Kind regards
Adrian
RE: Date Reference
Hi Adrian
Yes it will work even if the tab name changes.
The file name is the part in [brackets]. The tab name is the next part eg August 15. Remember to type the month name in cell A1 of the second file (not the INLAND PRICE DIFFS COPY file).
Doug
RE: Date Reference
Hi Doug
Unfortunately, the formula just stays as is.
I have put the formula in without the Indirect part, so as you state its showing as text within the cell. However, it doesn't pick up the month, it constantly shows A1, rather than June, July, August etc.
Kind regards
Adrian
RE: Date Reference
Hi Doug
Would it be easier if I sent you a mock up worksheets/books to an email address? Or are you able to see, what the issue may be?
Kind regards
Adrian
RE: Date Reference
Hi Doug
I have figured out the issue with how I was entering the formula. It works perfectly for referencing a new tab within a different workbook.
However, if I want it to reference an open workbook with its name partly based on the current month, how would the formula change ie
I want cell C6 in Workbook A to pick up cell B4 in sheet called 'Inland Diff' in Workbook B. Workbook B's name changes depending on the month ie
German Inland Diffs for July 2015
Similiar to the first issue, if I was to put in A1 of Workbook A, it would know which workbook to reference. Both workbooks will be open together, so perhaps it doesn't need to reference the name, but just the other open workbook?? However each month I don't want to keep amending the formula, because I assume it would pick up the name from last month
RE: Date Reference
Hi again Adrian
Yes you can make the filename dynamic.
I've attached 3 files to show how the link updates by changing the month in A1 and the sheet name in A3.
Open all 3 files first then in TestINDIRECT2 change A1 from July to August. Did it work?
Note the full formula is:
=INDIRECT("'[German Inland Diffs for "& A1 & ".xlsx]" & A3 &"'!$D$8")
Hope this helps with your last question.
Regards
Doug
Best STL
Attached files...
German Inland Diffs for August.xlsx
German Inland Diffs for July.xlsx
TestINDIRECT2.xlsx
RE: Date Reference
Hi Doug
Many thanks for the help. However, in your spreadsheet the INDIRECT example doesn't work. I have opened all files together, but there is a reference error. When I adapt for my purposes, the exact same issue occurs I am afraid.
Any ideas?
Kind regards
Adrian
RE: Date Reference
Hi Adrian
May be best to send your example.
You can choose Upload after replying.
Regards
Doug
Best STL
RE: Date Reference
Hi Adrian
Could you send to info@stl-training.co.uk and ask to forward to Doug Dunn.
Regards
Doug
RE: Date Reference
Hi Doug
That is fantastic , thank you so much its really appreciated, it now works. Its all these little points that I can continue to learn from and apply these formulas to other projects, so thank very much!!
Kind regards
Hi Adrian
Glad to hear it now works!
Doug
