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 Riny_van_Eekelen that the best practice is to not include the blank rows in the table because it does not play to the strength of the table - dynamic expansion.
If you must make do with the situation, you could use this to combine without blanks:
=LET(
header, Table1[#Headers],
stack, VSTACK(Table1, Table2, Table3),
cleaned, FILTER(stack, TAKE(stack, , 1) <> ""),
VSTACK(header, cleaned)
)