Forum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel ms training - Vlookup refering to another spreadsheet within another workbook
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
advanced excel ms training - Vlookup refering to another spreadsheet within another workbook
Resolved · Low Priority · Version Standard
Reply Fri 5 Oct 2007, 10:30Delegate Minnaar said...
Minnaar has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Vlookup refering to another spreadsheet within another workbook
I have cells refering to another spreadsheet within another workbook. This other workbook is changed on a weekly basis and therefor this workbook's name changes eg. Last week the workbook was called "20070928 6Week Programme" and this week a new workbook is created with the name "20071005 6Week Programme"
My original spreadsheet looks up the cell values in the other workbook which changes week on week.
Is there a way were I can have a cell with the reference name to which the look up can refer without me having to change the name of the lookup reference in each cell? The idea is just to change this one reference cell and then have all other cells change automatically?
For upcoming training course dates see: Pricing & availability
RE: Vlookup refering to another spreadsheet within another workb
Hi Minnaar
The solution to this would be the use of VBA code.
1. You would design a form where, when you opened the Workbook where the lookups run, it would pop up and ask you to enter the name of the target worksheet (eg 20071005 6Week Programme ).
2. This name would be saved to a variable in memory.
3. You would create macros to run all the lookups where the target data range would incorporate the variable in memory containing the sheet name.
This way every time you ran the lookup sheet the data would come from the sheet the user entered in the form.
Hope this helps
Regards
Carlos
|