How to nest references?

[GraterFang]

What I'm wanting to do is essentially use a reference to update yet another reference. In more detail, I want to retrieve a value on a worksheet and use that value to alter yet another reference to another worksheet.

For example, I want to retrieve a value (let say its 16 from either a local cell or a cell on another worksheet) and then retrieve yet another value from another worksheet based upon that value (lets say the worksheet I would want is then "Subject 16").


I've attempted various nesting formats of the typical reference formula but obviously have had no luck. Would I need to use VBA or is there a simple solution that I am missing? If I do have to use VBA how should I go about doing this? Thanks in advance. I'm sure there is a simple solution for this but I only have marginal experience with Excel.

[JBeaucaire]

The function you want is INDIRECT().

If you wish to create a formula equivalent to ='Subject 16'!B20 in a cell by having the "16" be in a cell (let's put it in A1), then this formula would indirectly create that formula:

=INDIRECT("'Subject " & A1 & "'!B20")

[DonkeyOte]

If you're saying the value (16) determines the sheet name from which the final value is to be retrieved from then you would need to use INDIRECT (note: this is a Volatile function - see link in sig. for more info)

B1: =INDIRECT("'Subject "&A1&"'!A1")

where A1 is your 16 (populated itself via a formula of some sort - you don't specify what), the above would return the contents of 'Subject 16'!A1

is that what you meant ?

(EDIT: we're at it again JB!)

[GraterFang]

Yeah perfect! Thanks guys!

I can tell I'm already in love with this forum

EDIT: For excel, INDIRECT shows a format of INDIRECT(ref_text, [a1]). This is obviously the right track but how do I accomodate these two fields? It doesn't seem that I need to so perhaps this is an optional field for arrays or something? In addition, is volatility truly a concern in this case?

[GraterFang]

New question:

With INDIRECT, how can I get excel to advance specific references like it normally does?

For example, when I apply this formula I would like the reference that points to the other worksheet advanced. Is this possible?

Thanks again!

[DonkeyOte]

Yeah perfect! Thanks guys!

I can tell I'm already in love with this forum

EDIT: For excel, INDIRECT shows a format of INDIRECT(ref_text, [a1]). This is obviously the right track but how do I accomodate these two fields? It doesn't seem that I need to so perhaps this is an optional field for arrays or something? In addition, is volatility truly a concern in this case?

The 2nd optional parameter is to denote referencing style be it A1 or R1C1, default is TRUE implying A1 style has been used to set the reference, eg:

=INDIRECT("A1") -- returns contents of cell A1

=INDIRECT("R1C1") -- returns error as no such cell reference exists using A1 notation

=INDIRECT("R1C1",FALSE) -- returns contents of Row 1 Column 1 (ie A1), the FALSE tells XL that R1C1 notation has been used.

Re: Volatility - if you have lots of Volatile Functions and/or you're using them in an Array (incl. SUMPRODUCT which you are not presently) then yes it can have serious impact on performance.

[DonkeyOte]

New question:

With INDIRECT, how can I get excel to advance specific references like it normally does?

For example, when I apply this formula I would like the reference that points to the other worksheet advanced. Is this possible?

Thanks again!

You will need to elaborate with examples, I don't really understand the question I'm afraid... do you mean you want to say increment the cell being returned (akin to use relative referencing) ?

Say A1 to return A1, A2 to return A2 etc... from variable sheet

B1: =INDIRECT("'Subject '!A"&ROWS(B$1:B1))
copied down B1 refers to A1, B2 to A2 etc etc...

[GraterFang]

Strange, your full reply doesn't show up until I attempt to quote you...

You will need to elaborate with examples, I don't really understand the question I'm afraid... do you mean you want to say increment the cell being returned (akin to use relative referencing) ?

Say A1 to return A1, A2 to return A2 etc... from variable sheet

B1: =INDIRECT("'Subject '!A"&ROWS(B$1:B1))
copied down B1 refers to A1, B2 to A2 etc etc...

Right (I think),

=INDIRECT("'Subject "&A1&" Results'!B1")

copied down becomes

=INDIRECT("'Subject "&A2&" Results'!B1")

whereas I want the exact opposite to occur where the formula becomes

=INDIRECT("'Subject "&A1&" Results'!B2")

Can I use a similar solution for this?

I appreciate the help btw

[DonkeyOte]

INDIRECT executes the text string constructed within outer parentheses as though it were entered as a formula directly... eg:

A1: Sheet1
B1: C10

C1: =INDIRECT("'"&A1&"'!"&B1)

the text string resulting from the above is

'Sheet1'!C10

the INDIRECT treats this as though you had entered:

='Sheet1'!C10

I trust that makes sense ?

Using your example and assuming first cell in which formula is used is say C3 (for sake of demo)

=INDIRECT("'Subject "&A1&" Results'!B"&ROWS(C$3:C3))

The row reference used for Column B in the resulting text string will therefore increment as you copy the formula down

With INDIRECT if you're ever unsure exactly what you're doing, remove the INDIRECT( ) from the formula, ie:

="'Subject "&A1&" Results'!B"&ROWS(C$3:C3)

the above will show you the references you're creating (without trying to execute them) so will help identify issues... if you're still struggling with the correct syntax of the text string, create a link manually - this will show you exactly what you need to replicate in your string formula.

[GraterFang]

The INDIRECT isn't throwing me off. I'm just unfamiliar with the functions/syntax
"& &"
and
&ROWS(C$3:C3)

I'm also unfamiliar with when XL does and does not increment references. I know that $A1 will lock the reference but that's all.

I am learning a lot about this today though

[DonkeyOte]

Using ampersand in this way is an alternative to the CONCATENATE function, eg

="a"&"pple"

Concatenates the two initially separate strings into one string - "apple"

Re: ROWS and any other Function - a good point of reference is XL Help (obviously) but also there is a useful Function Dictionary available (see link in my sig.) which is a file full of examples of how various functions are / can be used.
ROWS tallies the number of Rows present in a specified range, eg:

ROWS(C$3:C3) = 1 ... 1 row in range C$3:C3
ROWS(C$3:C13) = 10 ... 10 rows present in range C$3:C13

Re: Absolute / Relative Referencing, yes the $ holds either or both Row & Column refernece absolute such that as it is dragged it remains constant, the general premise with use of

ROWS(C$3:C3)

is that as you drag it down the first row reference remains constant whereas the latter increments, meaning the output increments by one each time it is dragged down

[GraterFang]

I see so
"& &"
allows the string to be broken so that a reference can be added? This makes sense but in that case how can I make the reference here absolute?

and you are using ROWS as a counter which is clever. However, out of curiousity, why include an ampersand before ROWS? EDIT AGAIN: Is this to append the final segment to the string? In which case do we not need the final set of quotation marks?

Thank you for your help! Thanks for the references also. I'll look at them so I don't have to ask so I hopefully don't have to ask so many annoying questions

[DonkeyOte]

Please Login or Register  to view this content.

With INDIRECT there's no need, remember you're essentially creating literal text strings, the INDIRECT then evaluates the string as a range - ie every reference is literal and thus absolute, ie

B1: =INDIRECT("$A1")

copied down to B2, B3 etc will all reference A1.. that's because the string is literal and the string being created is always the same... if you didn't encase within INDIRECT XL would not know "$A1" was meant to be a cell reference it would just assume it was a text string, like "apple" as such the text string does not "evolve" as it's copied ("apple" wouldn't become "banana" so why should "$A1" become anything other than "$A1" ?)

I'm not sure if this is making sense or not ?

why include an ampersand before ROWS?

Because we're creating a text string between the outer parentheses... going back to the A1 example, if we want B2 to reference A2 we need to alter the string from "A1" to "A2" (the string is not a range remember as far as XL is concerned, it's treated like writing "apple") ... so to do that we can use:

B1: ="A"&ROWS(B$1:B1)

this creates a text string of "A" and appends to that the result of the ROWS calculation, making one text string
[why no quotation marks ? this is a matter of coercion... the use of & automatically coerces all elements to text - ie 1 becomes "1"]

B1: ="A"&1 --> "A1"

copied to B2

B2: ="A"&ROWS(B$1:B2) --> "A2"

the encasing of the above within an INDIRECT tells XL to now translate the string into a Range reference, so =INDIRECT("A2") becomes =A2

Not much more I can add in truth... INIDRECT can take a little while to sink in... as mentioned before be wary of overuse as Volatile functions are in general not a good thing to use en masse

[GraterFang]

Great. The ampersand and string reply makes perfect sense and is what I came up with after I actually thought about it for a second.

However, while the reference A1 is absolute here as it is truly a string
B1: =INDIRECT("$A1")

the reference A1 here is relative as it increments for me in XL.
B1: =INDIRECT("'Subject "&A1&"'!C1")

Is this because the string is broken by the quotation marks and amended by the ampersands? How can I keep this latter A1 reference absolute?

[DonkeyOte]

the reference A1 here is relative as it increments for me in XL.
B1: =INDIRECT("'Subject "&A1&"'!C1")

Is this because the string is broken by the ampersands? How can I keep this latter A1 reference absolute?

Exactly... as it sits outside of text qualifiers (") it is seen as a range and increments as normal... you are to all intents and purposes mixing literal characters with formulae output to generate a text string... the resulting string is converted to range by INDIRECT...
so to keep reference to A1 as absolute as you copy the formula down/across you would use absolute markers as normal:

=INDIRECT("'"&Subject "&$A$1&!C1")

Note: copying the above down will always result in a reference to C1 on the sheet determined by A1 (in conjunction with Subject)

On an aside, please try to avoid quoting prior posts in their entirety as else the board can become cluttered, try to limit quotations to a specific point of interest / reference etc... I have modified the last post.

[GraterFang]

Great! Thanks so much for your help and patience! I'm pretty sure I have this down fairly well now thanks to your help. Also, I amended one of my previous posts as well to reduce the amount of quoted text. Thank you again!

[GraterFang]

Double Post -- Sorry