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?

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