LinkBack URL
About LinkBacks
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.
Please Login or Register to view this content.
Replace name in formula with cell reference
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.
Please Login or Register to view this content.
Last edited by narrowgate88; 09-15-2009 at 12:09 PM.
Maybe you want:
=INDIRECT("'L:\Life Right Resident Amortization Schedules\12 CCV\[12 Garden Homes.xls]"&D33&"'!$I$261")
but this will only work if workbook referenced is left open...
If you need closed, there is an addin available online to do that...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I tried the indirect formula with the referenced workbook open. I get the #ref error.
D33 says STARK, but it's actually a formula that creates it.
=LEFT(C33,FIND(",",C33)-1)
Does this still give error:
=INDIRECT("'L:\Life Right Resident Amortization Schedules\12 CCV\[12 Garden Homes.xls]"&TRIM(D33)&"'!$I$261")
and you are sure the entire path actually exists and sheet name is exactly that: STARK with no extra spaces in the tabname?
I just rechecked the path, and it is good. I am still getting the #ref message. Does it matter that I261 is also a formula? It didn't matter in my original formula, but maybe it matters in this one?
=I260-G261+H261
That should not matter... it is supposed to pull the data...
without having access to your path, it is hard to diagnose.
#REF usually means that the file or sheet does not exist...
D33 being a result of a formula should not matter either as long as the result is exactly the same as the sheet name it is referring to...
So if the original formula (without INDIRECT) works, then so should this one, if the workbook is open and if the result in D33 is indeed STARK with no additional spaces... did you try the second formula I gave?
...and, again, the referenced workbook must be open and in the same instance of Excel
I did try your second formula with the same result. I hadn't thought to make sure they were in the same instance of excel, so I'll check that now.
Genius. I didn't have them in the same instance of excel. Thank you!
Great. You are welcome.
Now if you do want to work with closed workbooks or in separate instances...then you can download and install the Morefunc.xll addin from here:
http://download.cnet.com/Morefunc/30...-10423159.html
and then use INDIRECT.EXT in place of INDIRECT in the formula I supplied...
Note: this addin can be embedded into the workbook so that it is shareable without having to download addin again.
Ok. Thank you for your help. The addin will definitely be useful as I don't normally have both files open.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks