Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)

[EagleOne]

2003


Need syntax (worksheet formula)

Contents of 'Sheet1'R711 = "XXX"

Obtained address of R711 via

MATCH(A11,'Sheet1'!$A$2:$A$1031,0)+1 which returned 711

I know the column is "R" so I used COLUMNS(R1) to force 18 so Column is 18 or "R"

On Sheet2 in Cell D1, I want the value "XXX" to appear which is the value in 'Sheet1'R711

How do I write the formula in Sheet2 in Cell D1 using the formulas MATCH and COLUMNS?

TIA EagleOne

 

[Dave Peterson]

Maybe...

=INDIRECT("'sheet1'!r"&MATCH(A11,'Sheet1'!$A$2:$A$1031,0)+1&"C18",FALSE)

This is basically:

=INDIRECT("'sheet1'!r711C18",FALSE)

Row 711, column 18)

 

[EagleOne]

Dave,

your formula:
=INDIRECT("'sheet1'!r"&MATCH(A11,'Sheet1'!$A$2:$A$1031,0)+1&"C18",FALSE) may have a typo

What about =INDIRECT!"'sheet1'!"&MATCH(A11,'Sheet1'!$A$2:$A$1031,0)+1&"C18",FALSE)

If above is OK how doI get the "18" into the formula i.e. Column(R1)?

What is getting me is the difference in concatenation VBA vs worksheet specifically when to include
or drop the "&". and /or when to use " or '

 

[EagleOne]

Dave,

Experimenting:

=INDIRECT(ADDRESS(MATCH(A11,'FirstFile 1st'!$A$2:$A$1031,0)+1,COLUMN(R1))) gives me the value on
Sheet2 R711 C 18 -- close but the wrong sheet

Then I tried =INDIRECT('FirstFile 1st'!ADDRESS(MATCH(A11,'FirstFile
1st'!$A$2:$A$1031,0)+1,COLUMN(R1))) and I get an error. Tried a & after ! to no avail.

So I got valid "return" but on the wrong sheet.

How do I get valid syntax to include the sheet name in the INDIRECT(ADDRESS(MATCH(..... formula?

EagleOne

 

[EagleOne]

Got it! THanks

=INDIRECT("'FirstFile 1st'!"&ADDRESS(MATCH(A11,'FirstFile 1st'!$A$2:$A$1031,0)+1,COLUMN(R1)))

 

[Dave Peterson]

C18 means column 18 in R1C1 reference style. And that's what the False at the
end specifies.

I tried your formula, but you dropped the open paren "(" right after the
=indirect function and replaced it with an exclamation point "!".

 

[Dave Peterson]

This still worked ok for me:

=INDIRECT("'FirstFile 1st'!r"
&MATCH(A11,'FirstFile 1st'!$A$2:$A$1031,0)+1&"C18",FALSE)

 

[EagleOne]

Thats interesting.

I missed the your concatenation visually. I saw ' "FirstFile but it was fine as " 'FirstFile .

I do not understand &"C18,FALSE. Obviously worksheet concatenation grags the "C" as Column where
VBA one would do a ....C"& 18 (or variablename).

As I said, there seems to be a difference in concatenation syntax between worksheet and vba. That
is what I learned today.

Thanks

 

[Dave Peterson]

I could use:
"C"&"18"
but it seems much more natural to use:
"c18"

On the other hand, since the row number had to be concatenated, I did use the &
operator.

=indirect() can take two forms.

The first uses A1 reference style:
=indirect(B9,true) or =indirect(B9)

The second uses R1C1 reference style:
=indirect(r9c2,false)
(row 9, column 2 is the as B2)

 

[EagleOne]

Thanks

I could use:
"C"&"18"
but it seems much more natural to use:
"c18"

On the other hand, since the row number had to be concatenated, I did use the &
operator.

=indirect() can take two forms.

The first uses A1 reference style:
=indirect(B9,true) or =indirect(B9)

The second uses R1C1 reference style:
=indirect(r9c2,false)
(row 9, column 2 is the as B2)