Forum Discussion

blackey's avatar
blackey
Copper Contributor
May 09, 2020

Creating a macro to pull information from one sheet to another

I have a workbook that has several sheets dealing with the stock market. Each sheet has one particular stock item except for the last sheet that lists all the stock with the purchased price and the c...
Macros and VBA
Charla74's avatar
Charla74
Iron Contributor
May 09, 2020

blackey 

 

Try this (Example attached):

 

=LOOKUP(2,1/(INDIRECT("'"&$A3&"'!B:B")<>""),INDIRECT("'"&$A3&"'!B:B"))

 

[Note: The formula uses the list to identify which sheet the lookup indexes, therefore each sheet name will need to be identical to the entries in the list - You should be able to drag down the formula for rest of list]

blackey's avatar
blackey
Copper Contributor
May 10, 2020
The formula is what I was looking for and I thank you, but how do I get it to work on other sheets. I does not show what sheet to pull from. If I copy it to the next cell, how does it know to pull from a different sheet when it does not have the sheet name in the formula.
  • Charla74's avatar
    Charla74
    Iron Contributor
    May 10, 2020

    As mentioned I the note, the sheet name is referenced from the list in column A (cell A3 in the example). The formula can be dragged down into the cells below but I didn’t do this as there is only one sheet so far. It’s for this reason that the list items must match the sheet names exactly. Hope this makes sense but let me know if still not clear.
    Alternatively, you could change it to reference the sheet by name:

    =LOOKUP(2,1/(CGC!B:B<>""),CGC!B:B)

    [Edited]

Resources