I have the following code which imports data from another workbook entitled LIONS. My question is how can I modify this so that the LIONS portion of the formula is taken from a cell, say A1?
=IF(ISERROR('P:\PAAR SHEET\Server Upload\20102011_01_NOVEMBER\[LIONS.xls]PUPIL DATA'!CK157),"",('P:\PAAR SHEET\Server Upload\20102011_01_NOVEMBER\[LIONS.xls]PUPIL DATA'!CK157))
Hi,
The "ck157" is the part that tells which cell the data is from.
If you wanted A1 then change the ck157 to A1 and see what happens.
One test is worth a thousand opinions.
Click the * below to say thanks.
Sorry think I've been miss understood, LIONS is the filename, but I have several filenames, I want the formula to take the filename from a cell so I don't have to a different formula out several times in each workbook.
I thougt something like this might work but it does not. Any suggestions gratefully recieved.=IF(ISERROR('P:\PAAR SHEET\Server Upload\20102011_01_NOVEMBER\[(A1).xls]PUPIL DATA'!CK157),"",('P:\PAAR SHEET\Server Upload\20102011_01_NOVEMBER\[(A1).xls]PUPIL DATA'!CK157))
Hello BobTheRocker,
You can use INDIRECT to create a reference by concatenating text and cell contents. But Indirect only works with open files, so you need all your possible files open.
=indirect("'P:\PAAR SHEET\Server Upload\20102011_01_NOVEMBER\["&A1&".xls]PUPIL DATA'!CK157")
Alternatively, you can use Indirect.Ext from the morefunc.xll, which works with closed workbooks and can be downloaded here: http://download.cnet.com/Morefunc/30...-10423159.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks