Forum Discussion
Automatically excluding cells with no data
All, I'm trying to create a robust real estate development model. It's laid out as follows: Input sheets (rent roll, other income, vacancy, operating expenses) Calculation sheet (simply aggreg...
If your records on the different sheets are aligned you could use a 3D range, in which case the following would consolidate the input:
= LET(
ORλ, LAMBDA(x, OR(x)),
stacked, VSTACK(input),
usedRow, BYROW(stacked <> "", ORλ),
FILTER(stacked, usedRow)
)[to show only complete records use ANDλ]
Otherwise if refresh on request were sufficient you could use PowerQuery.
Taking it a bit further
- Thank you for your help. I'm hoping a simple function will work but if it comes to it I'll use that.
