Source file is Excel with multiple tabs. The ...

references
(Tim B.) #1

Source file is Excel with multiple tabs. The last tab is “Results”. Results contains numerous rows with aggregate formulas such as “Days since last cleaning”, “Count of cleanings last 30 days”, etc.

calculated from data on other tabs.

The point of this tab is a sort of dashboard summarizing data.

It’s a clean sheet with only two columns. Name of metric in first column and second column a reference to other another tab with the domain functions to that do the heavy lifting.

The AppSheet data import will not pull any rows from this Results tab that contain these functions .

It will import rows with just text.

I do not need to write to this dataset, how can I just pull a static snapshot of the tab as this source?

Thank you.

(Reza Raoofi) #2

If I understood correctly, on this result tab your first column has a column header, but the second column doesn’t, and it only contains a formula? You might need to transpose the result tab and have a separate column for each aggregate formula, so AppSheet could recognize each calculated summary as a separate field for the read-only result table.

(Tim B.) #3

More info, Results tab:

Metric

Value Days Since…

=Dmax(… Count of…

=Dcount(…

First row is header Value column actually references field in another tab, but that filed contains a domain function as show .

(Aleksi Alkio) #4

I believe you are looking for a virtual column(s) which will show you values from this additional tab dynamically, correct?

(Reza Raoofi) #5

@Tim_B Exactly what I thought; in order to show those results in the app properly, I would transpose the result table; meaning have each metric in a separate column in a single row table, and column headers would be: [Days Since…], and [Count of…]

Then you could make a detail view based on that result table in your app.

(Tim B.) #6

Thank you, I transposed and put Metrics in columns and the first (only) row is imported.

However, AppSheet doesn’t pull in the value of the metric, it shows #VALUE! - but the Excel spreadsheet is properly evaluating within itself.

(Reza Raoofi) #7

I meant to have this and nothing else: Row1: Days Since…

Count of… Row2: =Dmax(…

=Dcount(…

(Tim B.) #8

Thanks, Reza… that’s exactly what I have done, but the row2 formulas are

returning #VALUE! in AppSheet.

However in Excel, the cells have the correct value.

(Reza Raoofi) #9

I usually use Google Sheet, so not sure if there is any issues with importing aggregate functions from Excel, but I just tested it on Google Sheet and added a simple tab named Result to the spreadsheet with nothing but a header and a DCOUNT(…) formula in its second row like screenshot below; AppSheet recognized it as Spreadsheet formula and successfully added a single row Result table; as you can see I left the criteria range in a separate sheet to keep the Result sheet clean containing 2 rows: