VBA macro

Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

xoggoth replied

29 October 2018, 23:09
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

Last edited by xoggoth; 29 October 2018, 23:13.
Leave a comment:
flamel replied

29 October 2018, 18:23
Originally posted by OwlHoot View Post

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
Leave a comment:
OwlHoot started a topic VBA macro

29 October 2018, 14:45
VBA macro

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!
Tags: None