Help needed with 'Indirect' function

**sumdumgai** · 03-05-2017, 10:49 AM

Hello. I've tried and tried but get nothing but #REF errors or incorrect results. Please help. Workbook contains several sheets, call them 'S1', 'S2', 'S3'. Sheets 'S2' and 'S3' each have a named column range, called 'C1'. Sheet 'S1' has 2 columns, named 'sName' and 'rNum'. I need an 'indirect' formula on 'S1' that returns the value on sheet(sName) at row(rNum) in column(C1). Something like:
[QUOTE]
=Indirect("'" & sname & "'!R" & rNum & "C" & Column(COLUMN(INDIRECT("'"&sName&"'!C1")).
/QUOTE]

Thanks.

**TMS** · 03-05-2017, 11:35 AM

You can't have a column range called C1; C1 is a cell reference for column C, row 1.

This formula:

Formula:

Please Login or Register  to view this content.

will return the value from column R for the row specified in the rnum range on the sheet in the sname range.

Not sure what the rest of it is meant to be doing.

**sumdumgai** · 03-05-2017, 05:06 PM

So sorry. Wasn't thinking when I called that column 'C1'. I know better. Lets say it's named 'wacadoo'. Then, I need a formula something like:
[CODE]
=Indirect("'" & sname & "'!R" & rNum & "C" & Column(COLUMN(INDIRECT("'"&sName&"'!wacadoo"))
/[CODE]

That formula, however, gives me an error.

In other words, the column number for the named range 'wacadoo' is different on sheets 'S2' and 'S3'. I need to get the value on sheet(sName) at row(rNum) on column 'wacadoo'. 'sName', 'rNum' values on stored on sheet 'S1'.

Sorry if I'm not making this clearer.

Thanks.

**TMS** · 03-05-2017, 05:43 PM

If you had three Named Ranges on sheet S1, say sName, rNum and cNum, you could use something like:

Formula:

Please Login or Register  to view this content.

You might be able to have the cNum as worksheet level Named Ranges but I haven't tested that approach.

**sumdumgai** · 03-05-2017, 08:47 PM

Thanks, but you misunderstand. There is a named column range on sheets 'S2' and 'S3'. However, on 'S2', the 'wacadoo' column is column 8; on 'S3', the 'wacadoo' column is column 10. I know what the named column range name is - it's 'wacadoo'. What I haven't been able to do is incorporate 'sName', 'rNum', whose values are on 'S1', together with the range name 'wacadoo' in one indirect formula. This is what I tried to do with this formula:
[CODE]
=Indirect("'" & sname & "'!R" & rNum & "C" & Column(COLUMN(INDIRECT("'"&sName&"'!wacadoo"))
/[CODE]

In this formula, 'Column(COLUMN(INDIRECT("'"&sName&"'!wacadoo")' produces the column number for the 'wacadoo' range on sheet 'sName'. I concatenate that to "C" giving "C8", so if rNum is 10, the resultant formula is "='S1'!R10C8". But that doesn't work.

Thanks.

**JeteMc** · 03-05-2017, 10:39 PM

I believe that in order to use the R1C1 reference style in an INDIRECT function you have to set the [a1] to FALSE, as in:

Formula:

Please Login or Register  to view this content.

This article may be helpful. Look at the section on arguments and parameters.
Let us know if you have any questions.

**protonLeah** · 03-06-2017, 12:01 AM

If you are trying to return a value instead of a formula, maybe:

Please Login or Register  to view this content.

1. Each instance of the named range wacadoo must be scoped to the sheet not the workbook.
2. If, as you say, wacadoo is a ONE column range on several sheets, you only need the row number to return a value.