Creating a macro to pull information from one sheet to another

%3CLINGO-SUB%20id%3D%22lingo-sub-1376723%22%20slang%3D%22en-US%22%3ECreating%20a%20macro%20to%20pull%20information%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1376723%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20that%20has%20several%20sheets%20dealing%20with%20the%20stock%20market.%20Each%20sheet%20has%20one%20particular%20stock%20item%20except%20for%20the%20last%20sheet%20that%20lists%20all%20the%20stock%20with%20the%20purchased%20price%20and%20the%20current%20price.%3C%2FP%3E%3CP%3EI%20want%20the%20last%20sheet%20(called%20%22Balance%22)%20to%20update%20every%20time%20I%20add%20data%20to%20the%20specific%20stock%20based%20on%20the%20date.%20See%20attached%20excel%20file.%20I%20have%20sent%20the%20file%20with%20only%20one%20of%20my%20stocks%20to%20make%20it%20smaller.%20On%20the%20balance%20sheet%20in%20cell%20C3%20I%20want%20it%20to%20show%20the%20amount%20that%20was%20inserted%20on%20on%205%2F7%2F2020%20and%20then%3C%2FP%3E%3CP%3Echanged%20to%20the%20data%20that%20was%20inserted%20on%205%2F8%2F2020%20then%20on%20down%20the%20line%20for%20the%20next%20date%20and%20so%20on%20and%20so%20forth.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1376723%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377134%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20macro%20to%20pull%20information%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659883%22%20target%3D%22_blank%22%3E%40blackey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20(Example%20attached)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOOKUP(2%2C1%2F(INDIRECT(%22'%22%26amp%3B%24A3%26amp%3B%22'!B%3AB%22)%26lt%3B%26gt%3B%22%22)%2CINDIRECT(%22'%22%26amp%3B%24A3%26amp%3B%22'!B%3AB%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BNote%3A%20The%20formula%20uses%20the%20list%20to%20identify%20which%20sheet%20the%20lookup%20indexes%2C%20therefore%20each%20sheet%20name%20will%20need%20to%20be%20identical%20to%20the%20entries%20in%20the%20list%20-%20You%20should%20be%20able%20to%20drag%20down%20the%20formula%20for%20rest%20of%20list%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377361%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20macro%20to%20pull%20information%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377361%22%20slang%3D%22en-US%22%3EThe%20formula%20is%20what%20I%20was%20looking%20for%20and%20I%20thank%20you%2C%20but%20how%20do%20I%20get%20it%20to%20work%20on%20other%20sheets.%20I%20does%20not%20show%20what%20sheet%20to%20pull%20from.%20If%20I%20copy%20it%20to%20the%20next%20cell%2C%20how%20does%20it%20know%20to%20pull%20from%20a%20different%20sheet%20when%20it%20does%20not%20have%20the%20sheet%20name%20in%20the%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377573%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20macro%20to%20pull%20information%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377573%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20mentioned%20I%20the%20note%2C%20the%20sheet%20name%20is%20referenced%20from%20the%20list%20in%20column%20A%20(cell%20A3%20in%20the%20example).%20The%20formula%20can%20be%20dragged%20down%20into%20the%20cells%20below%20but%20I%20didn%E2%80%99t%20do%20this%20as%20there%20is%20only%20one%20sheet%20so%20far.%20It%E2%80%99s%20for%20this%20reason%20that%20the%20list%20items%20must%20match%20the%20sheet%20names%20exactly.%20Hope%20this%20makes%20sense%20but%20let%20me%20know%20if%20still%20not%20clear.%3CBR%20%2F%3EAlternatively%2C%20you%20could%20change%20it%20to%20reference%20the%20sheet%20by%20name%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DLOOKUP(2%2C1%2F(CGC!B%3AB%26lt%3B%26gt%3B%22%22)%2CCGC!B%3AB)%3C%2FP%3E%3CP%3E%5BEdited%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 current price.

I want the last sheet (called "Balance") to update every time I add data to the specific stock based on the date. See attached excel file. I have sent the file with only one of my stocks to make it smaller. On the balance sheet in cell C3 I want it to show the amount that was inserted on on 5/7/2020 and then

changed to the data that was inserted on 5/8/2020 then on down the line for the next date and so on and so forth.

3 Replies
Highlighted

@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]

Highlighted
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.
Highlighted

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]