Moving non-numeric characters through INDEX

Frank Nelson

So, I'm trying to build an intake form that will let users fill out their information and then automatically grab that data and put it through a "Calculator", where it will then get pushed to a 3rd sheet for printing. The issue i'm running into, is that while I can use =INDEX[Example Item A]1:[Example ItemA]1 to grab the numbers from the first row in that column into a locked row further down and then link the calculator to those cells. This solution does not work (within my knowledge of the INDEX command) for words & checkboxes. This is a problem because the print sheet needs to be able to grab the names and contact email of the most recently submitted row as well. I've tried using INDEX(Match with cross-sheet references but either didn't understand how to adapt it to my usecase, or it doesn't fit it. It seems Match requires something on the destination sheet to match it to, and I don't have a reference since each form entry will be an entirely new person / order.

Example below of what i'm referring to, the entire sheet is over 20 columns of numerical questions I didn't think would be helpful to have added.

I think the ideal solution for my use case would be a way to use INDEX to move every cell from row 1 into this locked row to be referenced out, only because of consistency. But i'm open to any solution.

Brian Wilson DC

@Frank Nelson Frank, just a thought, because I can't see your "calculator", but instead of having your calculator below the row entries is there any way to build that functionality out on to additional columns that you -do not- include in the form for the person? That was as the data hits your sheet it does the calculations on your row and then simply uses the "Move/Copy Row to Another Sheet" automation? Then put the columns in order on your destination sheet in the order you want to see them and hide the irrelevant ones with the raw data?

Just a different way of getting there….maybe? :)

SSFeatures

Hi @Frank Nelson, I think what you're trying to do is definitely possible.

In this screenshot, in row 9, I reference all of the values in row 1 by using the INDEX formula.

These are the formulas.

=INDEX(Name:Name, 1)
=INDEX([Phone Number]:[Phone Number], 1)
=INDEX(Checkbox:Checkbox, 1)
=INDEX(Contact:Contact, 1)

So instead of this

=INDEX[Example Item A]1:[Example Item A]1)

You would want this:

=INDEX[Example Item A]:[Example Item A], 1)

However, when the values are in the same sheet, and I know the exact row, I would probably just prefer to use this formula, rather than the INDEX function.

=Name1
=[Phone Number]1
…etc

I hope this helps!