Cell References in lookups that refer to other sheets

What are you trying to do here? The second argument needs to be a range of cells on a single worksheet - it looks like you are trying to select cells from multiple worksheets?.?.? Please clarify.

It's hard for me to explain. Basically, I have a template for inn level budgeting, it grabs the data for each inn off of a master sheet and then saves itself on a shared drive. I want to add the targets for each inns to the sheet. They are on a shared drive as well, each within a larger regional file. I want my template to grab those targets, but the file paths change as the inns scroll through, so I need to find a way to use variables in the path name with the lookup. Each regional file is also a template with the same range names, I just need to be able have it change source book locations.
For instance, inn#123 might be in rg5 and the path would be something like
G:Forecast2\reg5\Filename.xls
but inn#525 might be in rg 15,
thus
G:Forecast2\reg15\Filename.xls
I need to use variables for 5 and 15.
KS

OK. Lets talk through this a little. So you have a large "Forecast" folder on a shared drive "G:". Within that folder, you have other folders "sec##" and in that, "reg##" wherein 2002 Budget Model.xls resides?

From your formula, it looks like you are trying to do a lookup on a range named "input" on the 2002 Budget Model workbook, and looking for the word "inn". The problem lies in the fact the path of the source workbook changes. The folder number variables are in data!a4 and a3. All sound correct? Let me know before we go any further.

Sounds like something for VBA instead of just plugging it in as a workbook function like you are trying.

By the way, as this gets further and further down the posts in the MrExcel forum, it gets harder to find. If you don't see me responding after a while, that could be why. You can email me if you need to.