How to nest references?

Originally Posted by 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!

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...

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

Originally Posted by DonkeyOte

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

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.

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

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

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

how can I make the reference here absolute?

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