+ Reply to Thread
Results 1 to 2 of 2

Form Responses Automatically sorted in Spreadsheet

  1. #1
    Registered User
    Join Date
    03-18-2019
    Location
    Australia
    MS-Off Ver
    7
    Posts
    17

    Form Responses Automatically sorted in Spreadsheet

    Hi Guys, Apologies if this is a basic question. I'm used to Excel and VBA, which I cannot utilise how I want to in Google Sheets.

    I have a form linked to a hidden spreadsheet to record responses. I've added a column with tickboxes. When ticked, I want the information displayed in another spreadsheet which is viewable by the public (who have the link).

    I have 2 queries. 1 I'm sure is easy, but I'm missing something obvious. The 2nd is probably impossible given Google Sheets limitations.

    1) The way I have the data linked now basically goes; If A2 is ticked (TRUE), then A2 on public sheet = B2 on hidden sheet (same heading just offset by the column with tick box). And dragged along to fill columns. Sweet. This works well. However, when I have (for example) Row 13 unticked, it leaves the row blank in the public sheet. Is there a way I can put this information into the public sheet and skip blanks? Ideally the filter options in the public sheet will not show blanks as an option too. Also I'd like column A sorted by Alphabetical order automatically.

    2) Is it possible to have the sheet either reset after each use, or not save live changes? Like if someone accesses it and filters it, then closes it. If someone else accesses it, it'll have the filter applied. I'd much rather the sheet be the same each time it's opened so no-one misses anything. I was hoping there was maybe a permissions setting where people can look and filter, but have no "write" access to where it's saved so that their changes aren't saved live.?

    Massively appreciate anyone who can help as I'm properly stuck on these aspects at the moment.

    Kind Regards,
    Cameron.

  2. #2
    Registered User
    Join Date
    02-20-2020
    Location
    Australia
    MS-Off Ver
    Office 365, 2013 & Google Sheets
    Posts
    20

    Re: Form Responses Automatically sorted in Spreadsheet

    Hi there, I have been doing similar things with forms of late.

    Correct me if wrong but
    1, other users input data via form
    2, you check boxes in hidden sheet
    3, data needed in alphabetical order of only checked boxes on a public sheet

    There are probably a few ways to get that effect but the simplest I can think of is via pivot table. They are a little clunky in sheets but have some advantages once you get the hang of them.

    Create a pivot from the form data filtering the checked boxes, utilise the 'repeat row labels option' to create a table type format & then use this data to reproduce onto the public sheet.

    As for the second part of your question, I believe I have that ability in a sheet but unsure which one. I can recall having the same issue but cant recall the fix. If I remember the sheet, i'll let you know.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1