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
- 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!
Reply to: VBA macro
Collapse
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.
Logging in...
Previously on "VBA macro"
Collapse
-
Originally posted by OwlHoot View PostI 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!
=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:
-
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
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: