Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

advanced excel ms training - vlookup refering another spreads

Forum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel ms training - Vlookup refering to another spreadsheet within another workbook

advanced excel ms training - Vlookup refering to another spreadsheet within another workbook

ResolvedVersion Standard

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?

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

Excel tip:

Closing all your workbooks Quickly

Hold the SHIFT key down and using the mouse click on the file menu, it will now now CLOSE ALL rather than close. This closes all workbooks down but still leaves the application open.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.