Jan 06 2021 02:44 PM
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.
Jan 06 2021 05:29 PM
Jan 07 2021 02:08 AM
That could be
=INDEX(INDIRECT("'"&B3&"'!B:B"), COUNTA(INDIRECT("'"&B3&"'!B:B"))-1)
if I understood the logic correctly.
Jan 07 2021 07:03 AM
Jan 07 2021 07:09 AM
Jan 07 2021 07:20 AM
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)
Jan 14 2021 06:14 PM
@Sergei Baklan I thought that the formula worked but then it did not. It came up with a $NAME?
I downloaded the file that you returned and got the message that it is in protected view and when I tell it to enable editing it pops up with the the $NAME? message.
I tried just copying the formula and pasting it in place and I still get the message.
Jan 15 2021 06:39 AM
Most probably you don't have XMATCH available for your version of Excel. On which version of Excel you are?
Protecting view - I guess you mean editing mode is not allowed, that's since you downloaded file from internet, kind of protection. Just enable it.
Jan 15 2021 08:23 AM
@Sergei Baklan I have Home and Student 2016 Version 2012 (Build 13530.20316).
When I enable the file it immediately pops up with the error message.
Jan 15 2021 09:07 AM
I see, 2016 has no recent functionality. Please try
=IFERROR(
INDEX(INDIRECT("'" & $B3 &"'!B:B"),
MATCH(1,INDEX(--(INDIRECT("'" & $B3 &"'!B:B")=""),0),0)-1),
"")
it's in column G of attached.
Jan 18 2021 02:43 AM - edited Jan 18 2021 03:24 AM
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.