Search code examples
google-sheetsgoogle-sheets-formulagoogle-forms

Google Sheets, cell referencing

I'm using a Google Forms with a checkbox template. To extract results, my current formula is:

=ArrayFormula(TRANSPOSE(TRIM(SPLIT(CONCATENATE(Responses!B17:17&","),","))))

I want to drag the formula horizontally, with B17:17 increasing to B18:18 instead of C17:17. Any way to do this?

Solution

  • I'm still looking at modifying your formula in the manner you've requested, but is it possible that the following formula also gives you the result you are aiming for? Please try it out and let me know. For testing purposes, I've limited its range; it can be adjusted as needed...

    =transpose(Responses!B17:Z41)

    UPDATE:

    I think the following formula is what you specifically asked for, a formula that could be dragged across. Please test it out and see if it works on your sheet. If not please highlight the problem with it in your sheet for us.

    =ArrayFormula(TRANSPOSE(TRIM(SPLIT(CONCATENATE(
   INDIRECT("'Responses'!B"& COLUMN(Q1) & ":" & COLUMN(Q1))
   & ","),","))))

    This uses your base formula, but changes the address range to use INDIRECT, and then uses the column values (as it is dragged sideways) to generate the row values of the range it is acting on in Responses.

    Note that the column Q was used to generate a starting value of 17, to create the initial B17 address in Responses. This column letter can be changed, in case you need to start somewhere else in the Responses range.The "Q" is incremented automatically by Sheets as the formula is dragged sideways, which increases the resuslting "row value" pulled from Responses.

    Let us know how this works for you.

    Results below; your formula in B2 and C2, draggable formula in E2:J2, and simple TRANSFORM in L2: enter image description here