| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Want A1 to receive contents of R (from MATCH()) and C (COLUMNS(R1)
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I combine data from two sheets where field contents match? | Excel Worksheet Functions | |||
| Table of Contents (Exact match Hyperlinks?) | Excel Worksheet Functions | |||
| How do I match and merger row contents from one workbook to anothe | Excel Discussion (Misc queries) | |||
| Can I set up excel columns that receive a checkmark? | New Users to Excel | |||
| Edit macro to match entire cell contents | Excel Discussion (Misc queries) | |||
Linear Mode
