Automatically excluding cells with no data

[developer8888]

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?

[#2]

@developer8888 

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.

[#3]

@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=10...

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!

[#4]

@developer8888 

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.

[#5]

@developer8888 

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.

[#6]

Taking it a bit further

[#7]

Sorry about that. This link should work:

https://docs.google.com/spreadsheets/d/13WJpbsOH4vm_L0_aoMcxzG3y1ua_xcTt/edit?usp=sharing&ouid=10335...

Thanks for your assistance.

[#8]

Thank you for your help. I'm hoping a simple function will work but if it comes to it I'll use that.

[#9]

@developer8888 

Now that I've looked at it, I don't see a solution using simple function(s) or formula(s). So if the solution offered by @Peter Bartholomew works, use it.

The only other thought I might put forth--which might require rethinking how you arrange the input and calculation sheets--would be to use Microsoft Word's MailMerge capability, with Excel as the data source, to produce that nicely formatted and organized printout. Years ago (decades ago, actually), when in my corporate position, I used MailMerge, with Excel as the data source, to print hundreds of personalized letters, letters with varying numbers of rows of data and other personalized paragraphs, when we were divesting a division and letting employees know what benefits they were entitled to.

That capability of Word would enable you to produce a nicer looking document. But, as noted, I suspect it would also require some extensive re-thinking of the Excel database components. So if @Peter's solution works, use it.

[#10]

@developer8888 

I think my previous answer focussed on bringing together multiple tables with similar positioning and layout.  If it is simply a matter of transferring non-black records to a holding table then the FILTER function could be more appropriate.

= FILTER(rentRoll, CHOOSECOLS(rentRoll, 1)<>"-")

[#11]

@developer8888 

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.