Forum Discussion
LET formula problem
In addition to the very valid observations by my friends Patrick2788 and Riny_van_Eekelen , I find myself wondering whether it's even necessary to have three separate sheets with exactly the same sets of columns.
If they are referring--a no doubt they are--to different entities, different products, people, services, whatever, you can still differentiate if you
- create a single table with those 12 columns you already have
- add a column that contains the Identifier of whatever it is that makes Sheet1 differ from Sheet 2, etc
So I've done that with the Sheet labeled "Combined" -- I haven't taken the time to go to your sheet 10 to work on your LET question. Just wanted to demonstrate a combined database.
With that combined database, for what it's worth, Excel could still separately report on each entity whenever desired--and do the composites and whatever else. And you can add new rows pertaining to any one of the entities in any order. Just identify them.
I agree with the others that you shouldn't need to filter blank records from Tables because they have no reason to be there in the first place.
= LET(
tables, VSTACK(Table1,Table2,Table3),
isEmptyλ, LAMBDA(v, OR(v<>"")),
data, FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
)
Where it is an issue is when one uses a 3D range (in order to accept addition sheets without changing the formulas), since this, by its nature, is likely to be oversized
= LET(
tables, VSTACK(Table3D),
isEmptyλ, LAMBDA(v, OR(v<>"")),
data, FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
)
Table3D
=Sheet1:Sheet3!$A$2:$L$20
These accept any records with data in one of the first 9 fields.
.. or with the pretty colours
= LET(
tables, VSTACK(Table3D),
isEmptyλ, LAMBDA(v, OR(v<>"")),
data, FILTER(tables, BYROW(TAKE(tables,,9), isEmptyλ) ),
VSTACK(Table1[#Headers], IFERROR(VALUE(data), data))
)
Table3D
=Sheet1:Sheet3!$A$2:$L$20I use direct cell referencing so rarely (here it is used to define a Name) that I thought I might as well re-enter the formula. Shame 'Sheet1' is not yellow but the salmon pink shows well if you have a taste for that kind of thing.