1 Reply Latest reply: May 15, 2014 9:55 AM by Data Kruncher RSS

    No Records Message


      Interesting idea here, but I have no idea how to go about doing it.  I have an excel spreadsheet with various tabs for regional managers, each with a summary.


      However, I've had a request to place a line that says "No Data Qualified" beneath the column headings if a given summary contains no data.


      Any thoughts or approaches?  Personally I think an emtpy summary tab is pretty clear that there was no data, but I thought I'd give the message board a try.

        • No Records Message
          Data Kruncher



          Assuming that:

          • your exported summary data resides on a tab named "Summary",

          • you expect column A to be populated with data, and

          • no data essentially means nothing appears on the worksheet,

          this formula will do the trick:

          [font="courier"] =IF((COUNTA(Summary!A:A)+COUNT(Summary!A:A))=0,"No Data Qualified","") /font[/quote]COUNTA counts the non-numeric cells in the column, while COUNT counts the numerics. Combining these in one formula covers all cases so that you can use the same formula for all datasets.


          In the end, the text will appear when no data exists, and the text will not appear when data does exist.





          Edit to adjust formula slightly.