VBA macro

[OwlHoot]

I have a multi-sheet Excel spreadsheet, and I need to set each cell of a column in one sheet to the address of the corresponding cell in a column of the same length in another sheet.

So for example if the sheets are called "Source" and "Target", and the columns are Range("B3:B10") in Source and Range("E5:E12") in the other, then cell E6 for example in "Target" should end up as =IF(ISBLANK(Source!B4),"",Source!B4) i.e. not the value itself but a reference to it.

I could probably work out a macro for myself, but seeing as I (thankfully) very rarely have to fiddle with Excel spreadsheets, I was hoping an expert could whump up a macro I can use for this.

Thanks in anticipation.

Easier still, I wonder if there is a way to do this via direct keystrokes. I tried a few things but couldn't get it anywhere near working!

[flamel]

I have a multi-sheet Excel spreadsheet, and I need to set each cell of a column in one sheet to the address of the corresponding cell in a column of the same length in another sheet.

So for example if the sheets are called "Source" and "Target", and the columns are Range("B3:B10") in Source and Range("E5:E12") in the other, then cell E6 for example in "Target" should end up as =IF(ISBLANK(Source!B4),"",Source!B4) i.e. not the value itself but a reference to it.

I could probably work out a macro for myself, but seeing as I (thankfully) very rarely have to fiddle with Excel spreadsheets, I was hoping an expert could whump up a macro I can use for this.

Thanks in anticipation.

Easier still, I wonder if there is a way to do this via direct keystrokes. I tried a few things but couldn't get it anywhere near working!

In Cell A1 of the tab "Source":
=RIGHT(CELL("filename",A2),LEN(CELL("filename",A2) )-FIND("]",CELL("filename",A2)))

That will give you the sheet name

In Cell E5 of the "Target" sheet:

=Source!$A$1&"!B3"

That will give the cell address
If you wanted a value from that, use the INDIRECT function
e.g. =indirect(e5)

I think that's what you're after - let me know if not

[xoggoth]

In Excel 2016 this works:

Format the target column as text.
Copy Source B3, paste as link to Target E5.
Remove $, ie change =Source!$B$3 to =Source!B3 and copy down to other cells.
F2 & enter on each cell will now update it to show as =Source!B3, =Source!B4 etc. rather than values.

That will take ages with lots of data but you can update a range in one go:
select cells to update
Data tab - Text to Columns, next, next, select text as Column Data Format, finish