Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the return from ADDRESS within another formula?
Hey all,
Wondering if I can get some help with this. Is there any way I can use the ADDRESS function within another formula? I have a formula right now which essentially tells me the start of a range I want to start an offset from So, this formula, using ADDRESS, currently returns "$B$33." Here's the formula in case you are interested: =ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1) (using two named ranges commandsxp and roomsxp) The locatoin returned from ADDRESS will vary and tell the start position of an array based on two string inputs. Anyway, what I want to do is use this returned value of $B$33 in an offset formula so I can start a new search from this location based on other criteria. So basically: =OFFSET(X,1,1), where X is the formula above denoting the reference cell to begin offsetting from! Any ideas how to get this "$B$33" used in this manner? Thanks! Only idea I've had so far is CELL("contents",Y) (Y being the B33) but this didn't work as it is coming back as text still. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the return from ADDRESS within another formula?
Alright, found something useful on the groups awhile back that I
forgot about. I am using this custom function (sorry, no credit since I can't recall who it was!) **** Function IDR(s As String) As Variant On Error Resume Next Set IDR = Application.Range(s) If Err.Number = 0 Then Exit Function Err.Clear IDR = Evaluate(s) If Err.Number = 0 Then Exit Function Err.Clear IDR = CVErr(xlErrRef) End Function **** So now, with address returning $B$33, I can use that return in my offset like so: =OFFSET(IDR(ADDRESS(MATCH(F14,commandsxp,0)+2,MATC H(E14,roomsxp,0)+1)), 1,1) Thank you whoever came up with IDR :) (IDR, IIRC, means "Indirect Done Right" - a method of using named ranges through the INDIRECT function while inside a vlookup.) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the return from ADDRESS within another formula?
Wrap it with Indirect():
=OFFSET(Indirect(ADDRESS(MATCH(F14,commandsxp,0)+2 ,MATCH(E14,roomsxp,0)+1)),1,1) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "S Davis" wrote in message ... Hey all, Wondering if I can get some help with this. Is there any way I can use the ADDRESS function within another formula? I have a formula right now which essentially tells me the start of a range I want to start an offset from So, this formula, using ADDRESS, currently returns "$B$33." Here's the formula in case you are interested: =ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1) (using two named ranges commandsxp and roomsxp) The locatoin returned from ADDRESS will vary and tell the start position of an array based on two string inputs. Anyway, what I want to do is use this returned value of $B$33 in an offset formula so I can start a new search from this location based on other criteria. So basically: =OFFSET(X,1,1), where X is the formula above denoting the reference cell to begin offsetting from! Any ideas how to get this "$B$33" used in this manner? Thanks! Only idea I've had so far is CELL("contents",Y) (Y being the B33) but this didn't work as it is coming back as text still. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the return from ADDRESS within another formula?
Look in Help for the INDIRECT() function
-- Kind regards, Niek Otten Microsoft MVP - Excel "S Davis" wrote in message ... Hey all, Wondering if I can get some help with this. Is there any way I can use the ADDRESS function within another formula? I have a formula right now which essentially tells me the start of a range I want to start an offset from So, this formula, using ADDRESS, currently returns "$B$33." Here's the formula in case you are interested: =ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1) (using two named ranges commandsxp and roomsxp) The locatoin returned from ADDRESS will vary and tell the start position of an array based on two string inputs. Anyway, what I want to do is use this returned value of $B$33 in an offset formula so I can start a new search from this location based on other criteria. So basically: =OFFSET(X,1,1), where X is the formula above denoting the reference cell to begin offsetting from! Any ideas how to get this "$B$33" used in this manner? Thanks! Only idea I've had so far is CELL("contents",Y) (Y being the B33) but this didn't work as it is coming back as text still. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the return from ADDRESS within another formula?
You can avoid ADDRESS, OFFSET and INDIRECT by using INDEX with your
original match functions, e.g. =INDEX(1:65536,MATCH(F14,commandsxp,0)+3,MATCH(E14 ,roomsxp,0)+2) On it's own this will return the contents of the cell (C34) in your example, or it can be used within other functions to return the cell reference |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the return from ADDRESS within another formula?
Thanks everyone for the responses. I may have been a bit unclear:
What I was trying to achieve by using the ADDRESS command was the return of "$B$33". This literal string I wanted to then use in a formula. Basically what I wanted to achieve was this: =offset($B$33,1,1) As you can see in the formula above, $B$33 is the reference cell (where the offset starts from). I was attempting to use the ADDRESS function to dynamically change the reference starter cell so that, based on other criteria, the location of the start of the offset could shift around the worksheet. So, $B$33 could be anything really based on other criteria. But we'll use $B$33 for discussion. Essentially, this then becomes: =offset(ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E1 4,roomsxp,0)+1),1,1) or (just to make it easy to read) =offset(address(33,2),1,1) [for simplicity, address(33,2) is equivalent to the dynamic shifting one] I use the simple example just to illustrate the point. Try =offset (address(33,2),1,1) in Excel. It does not work. This is because ADRESS returns a text string which can not literally be used within a formula as a reference, or at least, I have not yet found a way other than through VBA. The same way you could not expect =offset("$B$33",1,1) to work. As for indirect, combining indirect and address is useful in a lot of situations but not here. Simply because, indirect flows through what is returned by address. If $B$33 contained the word "dog" for instance, my offset formula would end up being: =offset(dog,1,1) Much the same way, if A1 were to contain: =ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1) .... and return "$B$33", =offset(A1,1,1) would not work as it starts the offset from A1, not the contents of A1. Hope that clears that up. Thanks for the suggestions with index. Im mucking around with it because Im plainly working with a terrible data setup, trying to basically do a lookup from a lookup from a lookup. Augh. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a cell address | Excel Worksheet Functions | |||
Address function to return values from min row | Excel Discussion (Misc queries) | |||
Lookup, and Return Cell Address | Excel Worksheet Functions | |||
Return Cell Address of MIN value - array formula | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |