• 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!

VBA macro

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    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!
    Work in the public sector? Read the IR35 FAQ here

    #2
    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
    Join Big Group - don't let them get away with it
    http://www.wttbiggroup.co.uk/

    Comment


      #3
      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.
      bloggoth

      If everything isn't black and white, I say, 'Why the hell not?'
      John Wayne (My guru, not to be confused with my beloved prophet Jeremy Clarkson)

      Comment

      Working...
      X