Share via

Microsoft Form Excel Response - New Data Populate on the Top Row

Anonymous
2023-11-22T21:34:32+00:00

We have a time out Microsoft Form that has a dynamic Excel linked for response results. In the Excel sheet, how do we get the new data to always populate on the top row using a "Sort" function and have the data calculate using the formulas from row below, please? Am I correct that the One Drive Excel isn't able to use macros?

Thank you!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-11-23T02:33:57+00:00

    Could you share us some dummy data and expected result? You may add index in your form and use SORT function - Microsoft Support to get the result on the top row for the newest data.

    Macro is only available when you open the file in desktop application no matter it is in OneDrive or not.

    0 comments
  2. Anonymous
    2023-11-28T17:33:23+00:00

    Could you share an expected result? What do you mean of newest data?

    You may add a help column combine date and time, then sort that column.

    =[Time]+[Date]

    0 comments
  3. Anonymous
    2023-11-28T18:01:26+00:00

    Hello - thanks for the response!

    I am trying to be able to open this Excel One Drive file so that the newest date from Microsoft Forms populates in Cell B2, rather than in B7 as is shown on the attached. (Microsoft Forms doesn't have a "time" option, but the date populates so that the time and date are carried over to the Excel form.)

    I have a "Sort" function on that column, but it doesn't seem to work upon reopening the file.

    0 comments
  4. Anonymous
    2023-11-28T18:17:39+00:00

    It seems it is by design for Form Responses. You may need to sort it again when you open that file.

    I'd suggest you send feature request to Microsoft. Hope they can add this feature at next version.

    Excel · Community (microsoft.com)

    Send feedback to Microsoft with the Feedback Hub app - Microsoft Support

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2023-11-28T17:18:58+00:00

    Thank you for your response - unexpected vacation kept me from viewing your response! Hopefully you can view the screen shots (thought they were attached to the initial help request).

    We have a sign out sheet in Microsoft forms:

    The OneDrive excel form uses the date (time) and duration to calculate: (=IF([@Duration]="1 Hour",[@Time]+0.04167,IF([@Duration]="2 Hours",[@Time]+0.08333,IF([@Duration]="4 Hours",[@Time]+0.167,"Tomorrow"))).

    However, the excel file's Column H's formula only populates down, and I cannot figure out how to have Column B to sort with the newest data on top.

    Any insight you may give is appreciated!

    0 comments