Return cell address

[novel24]

Hi. I'm looking for a way to return a cell address, that can be changed dynamically. In others words, my input on Sheet is two Values (F1,F2) that happen to be also be on a list in column A (although, not cell referenced). I would like to return below the F1,F2 values in sheet 2 there 'location' in sheet 2. However, those F1,F2 values will be changed frequently, but when I do change it, I want a cell that will place their location. So far I've written something that returns this:

[Method2.xlsm]Sheet1$A$2

where 'Method2' is the file name. However, I would like to use this addresses as part of a VBA script -- I don't need the method 2 sheet 1 part, only $A$2

 

[lenze]

Some sample code

Sub GetAddress()
With Sheet2.UsedRange
    Set c = .Find(Sheet1.Range("$F$1"), LookIn:=xlValues)
    MsgBox c.Address
End With
End Sub

You can use ActiveCell or Selection instead of a range
HTH
lenze

 

[VoG]

Do you mean something like this?

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:30px;" /><col style="width:30px;" /><col style="width:30px;" /><col style="width:30px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >D</td><td > </td><td > </td><td > </td><td > </td><td >K</td><td style="text-align:right; ">9</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >R</td><td > </td><td > </td><td > </td><td > </td><td >E</td><td style="text-align:right; ">16</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >Y</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >Z</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >V</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >J</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td >U</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td >A</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td >K</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td >X</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td >T</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td >P</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td >J</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td >X</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td >Z</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td >E</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td >D</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td >Q</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td >X</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td >H</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G1</td><td >=MATCH(F1,A:A,0)</td></tr><tr><td >G2</td><td >=MATCH(F2,A:A,0)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[novel24]

Do you mean something like this?

Sheet2

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 30px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td style="border: 3px outset rgb(240, 240, 240);">A</td><td style="border: 3px outset rgb(240, 240, 240);">B</td><td style="border: 3px outset rgb(240, 240, 240);">C</td><td style="border: 3px outset rgb(240, 240, 240);">D</td><td style="border: 3px outset rgb(240, 240, 240);">E</td><td style="border: 3px outset rgb(240, 240, 240);">F</td><td style="border: 3px outset rgb(240, 240, 240);">G</td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">1</td><td>D</td><td> </td><td> </td><td> </td><td> </td><td>K</td><td style="text-align: right;">9</td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">2</td><td>R</td><td> </td><td> </td><td> </td><td> </td><td>E</td><td style="text-align: right;">16</td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Y</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">4</td><td>Z</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">5</td><td>V</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">6</td><td>J</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">7</td><td>U</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">8</td><td>A</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">9</td><td>K</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">10</td><td>X</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">11</td><td>T</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">12</td><td>P</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">13</td><td>J</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">14</td><td>X</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">15</td><td>Z</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">16</td><td>E</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">17</td><td>D</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">18</td><td>Q</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">19</td><td>X</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">20</td><td>H</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>G1</td><td>=MATCH(F1,A:A,0)</td></tr><tr><td>G2</td><td>=MATCH(F2,A:A,0)</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

Not exactly; I've used that part of the match function in getting the original section. You have the idea with the setup. Rather, I want the output to be: A9 and A16 respectively.

 

[novel24]

Some sample code

Sub GetAddress()
With Sheet2.UsedRange
    Set c = .Find(Sheet1.Range("$F$1"), LookIn:=xlValues)
    MsgBox c.Address
End With
End Sub

You can use ActiveCell or Selection instead of a range
HTH
lenze

Hmm, this is an interesting direction, but I don't need a msgbox. I literally need a cell to say like A6 if the cell I lookedup is located at A6. In other words, the contents of F1 may be "A6". The reason I am doing this is because I am trying to build a dynamic column.

 

[VoG]

Try

=ADDRESS(MATCH(F1,A:A,0),1)

 

[novel24]

Try

=ADDRESS(MATCH(F1,A:A,0),1)

Yes, that works. That is the idea. I do have one problem, I'd like it to be functional in the following manner:

=ADDRESS(MATCH(I4,Sheet4!A:A,0),1)

That is, suppose the column of data is in a DIFFERENT sheet. This is the exact functionality I need. For some reason, it doesn't seem to work.

 

[Jobowo]

Is it not simply

[G1]="SheetX!A" & MATCH(F1,A:A,0)
[G2]="SheetX!A" & MATCH(F2,A:A,0)

?

 

[chuggins143]

Put it in the sheet with the list and then cut it out and paste it where ever you want it... I did that and came up with something that looks like this... =ADDRESS(MATCH(Sheet2!F1,Sheet2!A:A,0),1) This returns "$A$6", this was for the little list I put up where "g" was in A6 and "g" was put into F1.
Good luck,
Chad