Forum Discussion
Lookup Issue
I have a workbook with different worksheets for each stock that I own that keeps a daily record of the price to show how it does each day of the year. At the bottom of each sheet is a formula that take the column and and averages all the current data for the year.
I also have what I call my balance sheet that pulls the daily information from all my sheets onto one location for the current day.
I was given a formula to pull the current price each day { =LOOKUP(2,1/(INDIRECT("'"&$B3&"'!B:B")<>""),INDIRECT("'"&$B3&"'!B:B")) } and put it on the balance sheet. The problem I have is that at the bottom of each sheet is a formula that does an average for the year. It turns out that the formula above will pull the average total and not the current price for the day. How do I get it to exclude the average total.
13 Replies
Is there a reason for not updating to the 2007 technology of Tables?
{OK, even considering such things proves that I am not a dyed-in-the-wool spreadsheet buff!]
The existing formulas with INDIRECT and whole column references caused my computer (a core17, 64 bit, Surface laptop) become sluggish (a second or so delay following any change) even on such a trivial dataset.
That could be
=INDEX(INDIRECT("'"&B3&"'!B:B"), COUNTA(INDIRECT("'"&B3&"'!B:B"))-1)if I understood the logic correctly.
Do you have XMATCH() available for your version of Excel? When it could be
=INDEX(INDIRECT("'" & $B3 &"'!B:B"), XMATCH(,INDIRECT("'" & $B3 &"'!B:B"))-1)
- Any chance you can submit a sample file describing what you need.
Seems like putting a formula at the top of each sheet to give an average for the year and the latest price (by doing a SUMIFS that references the MAX of the date column) would be a simpler approach.
Would need to see how your file is laid out to give best advice
Thanks
Wyn
MVP
