ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1) (https://www.excelbanter.com/excel-discussion-misc-queries/195582-want-a1-receive-contents-r-match-c-columns-r1.html)

[email protected]
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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)





wrote:

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

[email protected]
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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$1 031,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 '




Dave Peterson wrote:

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)





wrote:

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

[email protected]
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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

Dave Peterson wrote:

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)





wrote:

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

[email protected]
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
Got it! THanks

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

wrote:

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

Dave Peterson wrote:

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)





wrote:

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
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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 "!".



wrote:

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$1 031,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 '

Dave Peterson wrote:

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)





wrote:

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

Dave Peterson
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
This still worked ok for me:

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



wrote:

Got it! THanks

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

 wrote:

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

Dave Peterson wrote:

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)





wrote:

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

[email protected]
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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 wrote:

This still worked ok for me:

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



wrote:

Got it! THanks

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

wrote:

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

Dave Peterson wrote:

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)





wrote:

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
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
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)




wrote:

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 wrote:

This still worked ok for me:

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



wrote:

Got it! THanks

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

 wrote:

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

Dave Peterson wrote:

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)





wrote:

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

[email protected]
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
 
Thanks

Dave Peterson wrote:

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)




wrote:

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 wrote:

This still worked ok for me:

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



wrote:

Got it! THanks

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

wrote:

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

Dave Peterson wrote:

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)





wrote:

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


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com