May 09 2020 08:20 AM
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.
May 09 2020 03:18 PM - edited May 09 2020 03:28 PM
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]
May 09 2020 08:06 PM
May 10 2020 12:46 AM - edited May 10 2020 02:08 AM
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]