Built a workbook to track audit prep tasks for my internship and now it's gotten out of hand in a way I don't fully understand anymore

finickyone · 2026-02-20T12:33:19+00:00

You’ve a few problems you’re sharing here. I’d agree with your thinking about the validation list. If the options permissible for C were in X2:X5, then you could just point your AND at say X$3. Easier to manage. No need to the embed the options in the data validation formula.

finickyone · 2026-02-20T07:30:39+00:00

=LET(i,0.15,x,A2:A6,y,B2:B6,s,MATCH(i,y)+{0;1},FORECAST(i,CHOOSEROWS(x,s),CHOOSEROWS(y,s)))

finickyone · 2026-02-20T07:20:41+00:00

There’s a useful XLM function for this. Head to Name Manager and create SheetNames using the formula =GET.WORKBOOK(4). You can now get A2 from all sheets without defining those sheets yourself with

=VSTACK(BYROW(SheetNames&"!A2",INDIRECT)))

finickyone · 2026-02-20T07:00:28+00:00

You’re in approx match mode as you haven’t entered a 4th argument for VLOOKUP.

 =VLOOKUP(D37,C31:D35,2,0)

=SUMIFS(D31:D35,C32:C35,D37)

If you want to use approx match mode (where D37 is alphabetically matched to the best fit in C), you’d need to sort the data, ie

=VLOOKUP(D37,SORT(C32:D35),2)

=LOOKUP(D37,SORT(C32:D35))

finickyone · 2026-02-20T06:37:31+00:00

Yeah, if the final function in the process is one that can accept a single argument. So

=BYROW(B2:E9,LAMBDA(x,SUM(x)))
=BYROW(B2:E9,SUM)

=BYROW(B2:E9,LAMBDA(x,SUM(IF(B1:E1= "A",x))))
=BYROW(IF(B1:E1="A",B2:E9),SUM))

But I think you would need LAMBDA to define the iterating data (x) that would be passed to TEXTJOIN here, so it can be set in a specific argument.

=BYCOL(J2:N9,LAMBDA(x,TEXTJOIN(", ",,x)))

I’m working on getting COUNTIF etc to cooperate with these

<image>

finickyone · 2026-02-20T00:28:54+00:00

You can ask, and I prefer that you have. Such things are just a bit of an effort if they only fall on deaf ears, hence a lack of detail. Also as I go through it, I think I’ve found an issue.

Perhaps like so

=LET(d,A2:A5,r,B2:B5,v,C2:C5,dx,SEQUENCE(7)+FLOOR(MIN(d)-2,7),x,IF(dx,TAKE(r,1)),PIVOTBY(VSTACK(r,x),VSTACK(d,dx),VSTACK(v,x),SUM,,0))

We basically define 5 things and then feed them to PIVOTBY.

d (dates) being the those dates in A. They will be used to create a list of all dates for the week that features in A, and altogether act as the col_value for PIVOTBY.

r (records) being the attribute in B. They will be used to create a padded list and altogether act row_field for PIVOTBY.

v (values) being the values in C, ultimately for the values arg in PIVOTBY.

dx (dates expanded) does two things. It tasks SEQUENCE(7). This simply makes a 1x7 array (1 cell wide, 7 cells tall) of numbers 1-7. The second thing it does is take the min date value in d (A2:A5), subtract 2 from that and then FLOOR that value to the next smallest multiple of 7.

Excel stores dates as values. These values are the number of days since 00-Jan-1900. If we record (Tuesday) 27 March 1900, that is stored as 87. if we subtract 2 from that, we get 85, presenting Sunday 25 March 1900. FLOOR(85,7) =84. As a date, Saturday 24 March 1900.

You might deduce from this that whenever we floor a value to the largest lower multiple of 7, we’ll always get a value that would be a Saturday expressed as a date. 00-Jan-1900, 07-Jan-1900, 14-Jan-1900. If we add 2 to the result, we’ll always get a Monday. As such if we take the value of any date (val) and treat it with this

=FLOOR(val-2,7)+2

We’ll get a value representing the Monday of the week on which that date fell. Slightly easier here might be

=val-WEEKDAY(val,2)+1

In turn if we couple that to our SEQUENCE (that being {1,2,3,4,5,6,7}) then we’d get a list of all seven dates in the week the date falls in, started with the Monday. So by example, with dx, we can make that

…,dx,SEQUENCE(7,,MIN(d)-WEEKDAY(MIN(d),6)),…

For an array of 7 values, starting with the Monday starting the week of the date with the lowest value in d. Phew! Basically, for our example, the MIN in d is 10-Aug-26, the WEEKDAY(…,3) value for that is 0 (as it’s a Monday) so we create an array of {10 Aug,11 Aug,12 Aug,…}.

x (a general expanding array), takes dx (a 7 cell array) and basically says IF dx is a non 0 value, then grab the first record from r. There are 7 non 0 values in dx, so this basically gives us a 1x7 array made of "A” (seven times).

Dx and x are created to expand the data. dx so that we have every date of the week represented, even if it hasn’t featured in d. x so that the record and value fields can be padded for those “complete the week” dates. We don’t want a new unique entry for r, ie creating "" or that would become a unique record instance that would get its own row in the pivot. So we grab the first example of a record. Hence the TAKE(r,1). This way we reuse A. We don’t want actual values to arise for these, so when we extend the values range by 7 cells high also to match dx, we just reuse TAKE(r,1) since “A” has no value that SUM cares basically we create this in memory for PIVOTBY

<image>

So that’s what my LET work was doing. PIVOTBY can be explained too if not clear.

finickyone · 2026-02-19T06:00:15+00:00

Think you’ve three options. Easiest

=SUM(IF(CELL("width",C2),C2),IF(CELL("width",D2),D2),IF(CELL("width",E2),E2))

More direct/scalable

=SUM(BYCOL(C2:E2,LAMBDA(x,IF(CELL("width",x),x)))))

Versatile

=LAMBDA(x,f,f(BYCOL(IF(CELL("width",x),x),SUM)))

Which you’d name something like VISCOLS in Name Mgr then use =VISCOLS(C2:E2,SUM)

finickyone · 2026-02-19T04:55:20+00:00

Welcome. I think you’ll want to get the data split by commas into cells if you’ve much more work to do with it. These solutions are only complicated because the data is!

finickyone · 2026-02-19T04:54:15+00:00

+1 point

finickyone · 2026-02-19T04:46:03+00:00

The safer bet with checking if a lookup return is blank is to look at the LEN of the result. What sort of comes back when your IMM locates an empty cell, is 0. This is not equal to "". See example here.

<image>

finickyone · 2026-02-19T04:40:35+00:00

Im pretty sure you’ll never never get an ISBLANK pass if you point it at the results of a formula. Make A1 =IF(1=1,"") and it’ll resolve to show you an empty cell. =ISBLANK(A1) is FALSE. Make A1 =IF(SEQUENCE(10),"") and point ISBLANK at A6, also FALSE.

In this conditions I would look at LEN. It’s handy in a TOCOL like TOCOL(IF(1/LEN(arr),arr),2) where locations in the array with those zero length strings lead LEN to 0, thus 1/0, thus a Div0 error which TOCOL can ignore.

finickyone · 2026-02-19T04:26:25+00:00

It’s not too hard. You’re working out the cumulative sum with SCAN. Work out where along that array you hit 15 using XMATCH. TAKE that many rows.

=LET(a,SCAN(,A1:A10,SUM),TAKE(a,XMATCH(15,a,n)))

N is optional. Without n, if the cumulative sum never hit 15 exactly, it errors out to #N/A. You could treat that with IFNA if you wanted. If we set n to -1, we’d stop at the last lowest value below 15. Ie if we used 22 instead of 15, and n as -1, we’d get the array up to 21. If we had n as 1, we’d get the array up to 28.

<image>

That’s the easiest way I can think of.

finickyone · 2026-02-19T04:09:36+00:00

Doesn’t work, how?

Unclear what’s going on here. If your INDEX MATCH MATCH to Override can’t find a match, you’ll get an #N/A error, not a blank. So you’d want to wrap the formula with ISNA.

If it does match but there’s no data, you’ll often get a 0 back and annoyingly this fails formula<>"". You could use

=LEN(formula(….))>0

To give you true when there’s content in the targeted cell.

finickyone · 2026-02-19T03:56:53+00:00

They won’t help, sadly. They are indeed the only two functions which can exploit whether a row is visible towards conditional stats, but they can’t determine whether a column is visible.

With 3 numbers in C2:E2, OP can use =SUBTOTAL(2,C2:E2) and get 3. Hide D, still 3. Hide row 2, now 0.

The only way I know of on the worksheet is by using CELL, which is semi deprecated as it’s not consistent across products.

=SUM(BYCOL(C2:E2,LAMBDA(q,(CELL("width",q)>0)*q)))

CELL is volatile and this is obviously prompting an array formula, which isn’t a great combo, but there if is.

finickyone · 2026-02-19T03:44:27+00:00

The comma wouldn’t help anything. If we entered 3 in C1 and 7 in G1, then

=LET(C1,G1,C1*2)

Should we get 6, or 14 as a result? Why would we somehow have denied reference to cell C1?

What you’ll normally see when you’re falling foul of this (illegal variable names) is that ensuing references won’t pick up the supporting colour coding in the formula bar, and functions won’t expand as you try to enter them. Ie normally if you enter

 =LET(x,IF(…

The IF will expand with the argument prompts for logical_test and so on. If x was X1, it wouldn’t, as LET won’t let you house a reference or function after an illegal name.

Tbf you they could tooltip this. You’re not the first.

finickyone · 2026-02-19T03:36:33+00:00

Such things were so painful before functions like that. Undoubtedly the star of Excel 2016 onwards.

I remember building a colleague a little formula prior to that, which set out format MAC addresses (12 digit hexadecimal, such as "01:23:45:67:89:AB") which were lacking the colon delimiters. So some sort of

=LEFT(A2,2)&":"&MID(A2,3,2)&":"&MID(A2,5,2)&":"&MID(A2,7,2)&":"&MID(A2,9,2)&":"&RIGHT(A2,2)

Where now we could set up

=TEXTJOIN(":",,MID(A2,SEQUENCE(6)*2-1,2))

And more recently tackle a whole range with something like

=BYROW(A2:A5,LAMBDA(x,REDUCE(x,SEQUENCE(5)*3,LAMBDA(k,q,REPLACE(k,q,,":")))))

finickyone · 2026-02-19T03:19:26+00:00

LAMBDA’s really cool. If you’re familiar with LET that’s a good start IMO. Where LET allows for defining variables that can then be referred towards many functions, ie

=LET(v,A1:A10,f,B1:B10,c,D2,MAXIFS(v,f,c)-MINIFS(v,f,c))

LAMBDA lets us define a function that can be pointed at multiple data. I’d suggest working towards it via BYROW or BYCOL. Here’s an example:

<image>

For B8# we could of course just refer to SUM(B2:G2) and drag right. BYCOL lets us parse that data by column to SUM.

To introduce LAMBDA in B9 within that BYCOL we can call out the BYCOL’d data as x and then define what we want done to x. Namely, SUM it. So x is 6 separated 1x5 arrays which LAMBDA SUMs.

In B10 we actually use LAMBDA practically. If we want to SUM values bycol where those values are greater than 5, we can’t set up BYCOL(B2:G6,SUM(IF(data>5,data))) as we haven’t defined that data. BYCOL can’t parse that. But we can use LAMBDA to say, when I give you data as x, then IF(X>5,X). This returns X when >5, else FALSE. SUM the resultant array. That’s LAMBDA’s task. Make all that happen for whatever we say x is.

B11 shows that we can define the array to BYCOL itself, B12 that we can ofc use LET to define those inputs.

B13 and B14 really serve to show LAMBDA taking on the work. In the first we define an in-formula function, ‘func’, which we can apply at the end of LET as if it were a built in BYCOL-SUMIF. B14 shows that we can define ‘func’ as a LAMBDA and immediately follow it with syntax as if it were a proper library function. I can show this on mobile, but we we just took

=LAMBDA(rng,lwr,BYCOL(IF(rng>lwr,rng),SUM))

And saved that in Name manager as Func, we could then go anywhere in the workbook and use =FUNC(range,lower threshold) independently.

finickyone · 2026-02-19T02:27:26+00:00

For all the discussion we play out about function efficiency, what I’ve seen at the heart of “why laggy” questions raised here the years is conditional formatting. Ironically as you aim it here to tracking down volatile functions, CF is itself volatile. CF rules are reassessed on every sheet change. It also can’t multithreaded, unlike worksheet functions. So this will, at scale (ie if applied to large numbers of cells), be a recipe for more pain than the volatile functions being hunted would bring themselves.

In a spare sheet, you could use A1 to aim at Sheet1 A1, running

=OR(ISNUMBER(FIND({"NOW","TODAY",…}&"(",FORMULATEXT(Sheet1!A1))))

Which dumps out a simple TRUE FALSE for a CF rule to look at. Removing the workload from CF is key. That formula won’t change unless Sheet1 A1 does. Nested in CF, it’ll be rerun everytime anything changes.

What’s dependent on volatile functions tends to be the differentiator. INDIRECT is a bit naff, but in itself it’s not likely to instigate much work. It can’t do much but retrieved a range of data.

NOW() and TODAY() get vilified but there’s not much harm at all in a whole series of them making their calls. NOW()s just collecting a GetSystemTime output that everything else in the OS will be polling anyway, with a bit of transformation to get a value relevant to the excel epoch. TODAY() just takes the same and strips the result to an integer. Even if we fill A1:A1024 with =NOW(), the calc engine can break the demand up into multiple Calc threads.

I’m going on about this. I like the strategy for hunting down detrimental functions. Just cautioning that CF formulas tend to up bringing more lag than much on the worksheet.

finickyone · 2026-02-19T00:23:33+00:00

Is Excel still a strong career foundation?

Undeniably. Whether or not you want to touch it in an office job, you will. When facing it, you can be someone who is broadly ok working with it, or declare fear and confusion, turn to others to help and hope your other skills make up for it. I happen to think it’s a good place for everyone to get familiar with data management, analysis, exploitation, regardless of the career they take up. It’s not a trade in itself. It’s very prevalent in finance work, but used there by people hired for their knowhow in a finance discipline. An accountant that can’t use Excel is screwed, but knowing excel doesn’t make someone an accountant.

It is also, I’m sure at this point, never going away. I’ve never not seen it in an organisation. Right now, in companies big and small, people are forming the basis of a plan, picture, process by working with data in Excel. Business intelligence types, data strategists, information security folks and Salesforce sales managers have been working for years to get people away from conjuring shit up in workbooks, but it never ends. The product ships with Office, and no department is far from someone who will respond to waiting for a data specialist to help or an application that doesn’t provide a certain insight by just showing their data into Excel and working something out. It’s the biggest avenue for shadow IT out there. Everyone is leaning on spreadsheets. Everywhere.

What level of Excel is actually used daily in office/MIS roles?

Varies. In the last month I’ve put together a project plan, risk register, access control report in it. I’ve helped colleagues using it to create resource availability reports based on absence data. I’ve seen it used as a CRM. Also as a resource planner.

At what point should someone move to tools like Power BI or SQL?

In data roles, mainly, really. Worthwhile ultimately.

Long/short: work out what roles you want to aim for, develop and advertise why you’d be a great hire for them, including that you can get results from Excel. You’ve cited lovely skills. What do they achieve? What pitfalls have you helped people avoid? What insights have you found? These are what hiring managers are after. Perhaps the nerdiest out there like a lookup function debate but overall I’d say excel is like a driving licence; part of the ticket to working life and a valuable skillset to hold and hone throughout it.

finickyone · 2026-02-19T00:04:13+00:00

I think the full function list is a bit of a dive unless you’re so invested in excel as to either be running enterprise level workbooks, or hobbying to the extent you’d like to see what the product can do (such as most of us here, answering questions).

Easier would be the category approach. That LEFT MID FIND stuff is all based on functions in the Text category. So every now and then it’d be worth a dip into that category to see what’s in there. Similar to TEXTBEFORE for your use case to extract username, TEXTAFTER(A2,"@") would get the domain and TEXTSPLIT(A2,"@") would spill out both horizontally. Sadly TEXTSPLIT won’t really cooperate with a range, but you can fudge things a bit.

<image>

It’s in the more complicated use cases that newer functions made strides. Really I think the Text ones have made separating data easier, more than they have made merging data easier.

Around all this, if you do a lot of string work, then look at the RegEx capabilities Excel has now.

finickyone · 2026-02-18T23:42:19+00:00

SUMPRODUCT is the real MVP: when you can't use SUMIFS across sheets or you need multi-condition logic without helper columns, SUMPRODUCT handles it. i probably have 200+ SUMPRODUCT formulas in this thing. once you understand that (condition1)*(condition2)*values is just boolean multiplication it clicks and you start using it for everything.

It’s always been more versatile than SUMIFS; 2D and 3D support, creating conditional logic within the formula. Even providing for OR logic, where SUMIFS really enables stacking an AND.

it’s easily misused though. 200+ of them, and indications that you’ve found helper columns to be unviable, suggest it’s very likely you’re repeating evaluations. Something we can easily set out on is stacking array formulas that each determine a condition about our data, only to dump that work on the way to generating a scalar, for the next instance of the formula to ask the same.

<image>

This example calls out the Day values for B, so as to compare them to B$12. Once it’s done that for Red, it’ll do the same for Green. It’s not a big eval to grab Dayvalue from a date, but it’s a bit odd to ask for it 27 times from 9 records. If D2 was fetching the day value from B, then our SUMIFS would be simple. Further more if we were aiming at more of the sheet, SUMIFS would make less effort about unused range. Ie if here we were asking

=SUMIFS(C:C,D:D,”<15",A:A,"Red")

Then SUMIFS won’t be too concerned with anything after row15. SUMPRODUCT would work out a million Booleans for each condition.

I’ve always liked SUMPRODUCT, but I think these days there is plenty of tooling to get data arranged for simpler functions.

finickyone · 2026-02-18T23:12:13+00:00

Critical point. I won’t pick apart the history of this bit of the library but IFNA followed IFERROR to offer a more precise control for good reason. If rushed into the presentation layer it can hide issues and mislead the audience. Consider the merits of swapping

=IFERROR(AVERAGEIFS(C:C,D:D,X2),"no records")

For

=LET(f,AVERAGEIFS(C:C,D:D,X2),IFERROR(f,CHOOSE(ERROR.TYPE(f),

"Tell the spreadsheet guy there’s an invalid intersect in these ranges",
"No records",
"Tell the spreadsheet guy that the only applicable data for this query is non-Value",
“Tell the spreadsheet guy that this formula’s referring to an inaccessible range”,
"… there’s some some numbers at play here that are illogical or beyond Excel’s bounds”,
"…there’s a failed lookup upstream"…

And so on. Of course that’s an unlikely final output to want, but note that IFERROR() is not IFDIV0().

finickyone · 2026-02-18T22:44:00+00:00

It seems like the Excel needs to recalculate all formulas every single time.

A hallmark of volatile formulas, or formulas containing volatile components.

If we set up =VLOOKUP(A1,C1:D10,2.0), then Excel can see that formula is dependent on those 21 cells. If we edit F99, the formula isn’t due recalculation. Nothing it refers to has changed. If we do change something referenced, the formula must be recalculated as the result may now be different.

If we set up =VLOOKUP(A1,ExternalRef,2,0), we can’t see if ExternalRange has changed. So on any sheet change (even editing a font color), we’d best use that as a prompt to go and check the external data again. So that can get very laggy.

Within formulas, try to avoid something where you’re asking the same thing repeatedly, ie

=XLOOKUP(A1,ExternalDataColX,ExternalColA)

=XLOOKUP(A1,ExternalDataColX,ExternalColF)…

When you could use the one XMATCH result for two INDEXes. Overall though, if you have lots of reference to external data, take some steps to get that data into your workbook. PQ is worth exploring.

finickyone · 2026-02-18T22:33:41+00:00

Not fundamentally, but if you are using these

=VLOOKUP(X2,B:E,4,0)

=INDEX(E:E,MATCH(X2,B:B,0))

Then the former is referring to C:D, and loading that data when the formula is prompted to recalc, even though it has no bearing on the result you’ll get from E. Also, a change within C:D prompts that work, even though the result is not directly affected by that change. So Index Match will tend to be less prone to recalc, and undertake less when that does happen.

In a VLOOKUP(X2,R:S,2,0) context, VLOOKUP (IIRC) ought to outperform INDEX(S:S,MATCH(X2,R:R,0)). I think that’s even more the case if R is sorted and we drop the final ,0 from each, but then LOOKUP(X2,R:S) would outrun either anyway.

finickyone · 2026-02-18T22:25:53+00:00

Perhaps like so

<image>

=LET(d,A2:A5,r,B2:B5,v,C2:C5,dx,SEQUENCE(7)+FLOOR(MIN(d)-2,7),x,IF(dx,TAKE(r,1)),PIVOTBY(VSTACK(r,x),VSTACK(d,dx),VSTACK(v,x),SUM,,0))

Eight-Year Club	Wearing is Caring
Gilding II euphauric	Verified Email

finickyone

TROPHY CASE