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 aggregated all inputs into rough pro-forma)
- Output sheet (I need it to exclude unused cells and present a clean pro-forma)
On the input side the number of floorplans will vary from deal to deal. I've included 20 rows for inputs on the rent roll input page. Let's say one project only has five floorplans, I don't want there being a bunch of blank rows on the output sheet. Is there a way to only show cells that have data inputed and automatically adjust the number of rows?
10 Replies
I think this is spiralling out of control. To produce one table for your output sheet requires
= LET( table, FILTER(rentRoll, CHOOSECOLS(rentRoll, 1)<>"-"), numUnits, CHOOSECOLS(table, 3), unitMix, CHOOSECOLS(table, 4), acSF, CHOOSECOLS(table, 7), grSF, CHOOSECOLS(table, 9), rent, CHOOSECOLS(table, 10), mInc, CHOOSECOLS(table, 12), sumUnits, SUM(numUnits), sumUnMix, SUM(unitMix), wtAVacSF, SUM(numUnits * acSF) / sumUnits, wtAVgrSF, SUM(numUnits * grSF) / sumUnits, wtAVrent, SUM(numUnits * rent) / sumUnits, incPerSF, wtAVrent / wtAVacSF, sumMoInc, SUM(mInc), sumAnInc, 12*sumMoInc, VSTACK(rentRollHdr, table, HSTACK("Totals / Averages", "", sumUnits, sumUnMix, "", "", wtAVacSF, "", "", wtAVrent, incPerSF, sumMoInc, sumAnInc) ) )to allow for the differences in the totals row.
An alternative strategy would be to use Excel Tables for all input. The tables resize to accommodate the data as it is input so the filtering is no longer needed and the column totals move with the data.
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.
It's refreshing to see that you're already aware of the value of separating input from output. The way you write, it does sound as if you have at least a start at the workbook you want to end with. Rather than just verbally describing it and asking for help in completing it, is it possible for you to post a copy of the actual spreadsheet (not an image; an actual working workbook in its current state). Post it on OneDrive or GoogleDrive and post a link here that grants access to it. If all you want to know is whether it's possible for the output sheet to exclude blank rows, the answer is "Yes." If you want more help in how to accomplish that, it would help us help you to see what you're working with. As is often the case, the how-to often depends on the what-is.
mathetes thank you for your assistance. Here's a link to what I currently have:
https://docs.google.com/spreadsheets/d/13WJpbsOH4vm_L0_aoMcxzG3y1ua_xcTt/edit?usp=drive_link&ouid=103354072530995658355&rtpof=true&sd=true
On that final page I'd like for the unused rows to disappear so I can put it directly in an OM without editing it. I have a feeling this will be a VBA task but I could be wrong.
Thanks again for you help!
Clicking on that link yields this result:
So you'll need to post a link that grants access.
And i doubt that VBA will be needed; at least, my going-in premise is that a lot of things that maybe used to require VBA or macros can now be done with some of the newer functions. In this case, for example--sight unseen, to be sure--I'm expecting that FILTER might work.
