I will preface this by saying that I don't believe it's an elegant method by any stretch, but it might be helpful for those who author forms in Excel and can't use the API to automatically update them. As long as your version of Excel supports Power Query with OData as a data source and your formula calculation option is automatic it will work.
Self_Updating_Form.xlsx (40.7 KB)
In this example query I have simply returned all values and added a column that populates with an emoji for the item status value. You could easily build upon this to do other things, such as,
- Filter out rejected/has issues entries,
- Remove all the columns that aren't needed for a smaller query table
- Add other queries to calculate progress totals, percentages, x of y complete etc
- Or calculate this inside the sheet and not in power query.
- Hide values entirely by populating a value to use as a choice filter once they are in an approved state
- Show the most recent value submitted for a question in the
labelsfor that question's choices, as reference when it is being updated and resubmitted - Update question
labelsorhintswith values/totals/avg-max-min of previously submitted values
Intent:
- Provide simple offline, on device filtering of submitted items so the default view shows those that have not been submitted. But do not remove them entirely in case a repeat or rework is needed.
- Provide a basic and accessible way to update the form definition semi-automatically to show progress/status to the user.
- Keep everything contained within one XLSX file. (If you use external selects you will need to build on this to write out updated CSVs)
Method
- For on device filtering, append the last submitted item ID to a
last-savedvalue for the submitted item in acalculate, building up a string of submitted IDs, then apply a choice filter that checks for the ID within the string to supress it.- thx to @LN for this workaround tip
- For form updating, embed a Power Query in the form that pulls the submissions for that form, then create dynamic choice
labelsby counting items within a group that match / searching for a particular item in the submissions table- The starting
labelis stored in the columnbaselabel, then the actuallabelis an excel formula that combines the status emoji with the starting value
- The starting
To set it up:
- Publish the form and copy the OData URL
- Paste this URL into the form in the area indicated in the Settings worksheet
- Go to Data on the ribbon and 'refresh all', the first time you may need to enter your Central user/password under 'basic authentication'
- If there are no submissions yet, the table on the Submissions worksheet will be empty.
No further action - If there are submissions, the table will populate and the choice labels in the Choices worksheet will update
Save the definition, upload to Central and test/publish/sync. (No need to change version number, it timestamps automatically) -
Repeat as frequently as needed.
To use the form
On device, before any form definition updates, after completing a submission for an item, the next time the form is opened, those item(s) will still show a 
'not started' flag, but will be hidden unless 'show already submitted items' is selected. Note that Enketo is different to Collect here, after updating the definition (or reconfiguring the form), the last-saved value used to hide these items will reset in Collect but not in Enketo.
After a definition update, the emojis will reflect the group progress and the item status.
Enketo screenshots
Eg, after selecting (D), (D3), (D3_a) and submitting the form, option (D3_a) is now removed from the base item selections:
But submitted items can still be displayed, which will then display (D3_a):
Once some submissions have been received and the form is opened and the query run, the choice list will update and replace the with 
for those sites/subsites that are in progress, and indicate individual item progress depending on their status in Central:
-
Submitted (
on Collect) -
Approved -
Has issues -
Rejected
The screenshots below show the form after running the query and doing a definition update with the following submissions uploaded to Central:
After submitting some items (A1_a/c/e/f & C2_a) and running the definition update and publishing th new version, the is now for A, C, A1, C2. As mentioned above, in Collect post definition update their status emoji would change, but the submitted items would not be hidden as 'already submitted' and would all show by default.
Toggling to show the submitted items in Enketo and their status emoji :
