Need to get cell address and insert formula reference to it

[paulma1960]

Hi all, in the sample file, I have a column of values on Sheet1.
On Sheet 2, I have a column that references the column on Sheet1.
I'm looking for some code that, given the lookup value (in this case AB02, which is on cell Sheet1!B4), would insert in cells Sheet2!B11:B14, the formula =Sheet1!B$4.

All suggestions appreciated. TIA, Paul

[bebo021999]

Sheet2, B3:B6 = "AB01"
Sheet2, B7:B10 = "AB05"
Sheet2, B11:B14 = ???
How we know it will be "AB02"?
If there was no rule, just input: "AB02" in B11:B14
???

[jindon]

I'm looking for some code that, given the lookup value (in this case AB02, which is on cell Sheet1!B4), would insert in cells Sheet2!B11:B14, the formula =Sheet1!B$4.

Simple exsmple

Please Login or Register  to view this content.

[paulma1960]

@jindon, thanks, that works fine. I modified the code as follows:

Please Login or Register  to view this content.