Question description:
This user has given permission to use the problem statement for this
blog.
I have a google sheet that takes information from a google form, that creates a row every time a form is submitted. Since it is a new row that is created, rather than filling in the next row down, the formulas I have applied to some of the columns do not work. The same is true when I try and link the data to another sheet to bypass this problem. This is probably quite a general functionality issue so I won't send the actual document but let me know if you need it
Solved by F. F. in 13 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/08/2018 - 11:22
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
07/08/2018 - 11:23
So can you help?
User
07/08/2018 - 11:23
do you understand my question
Excelchat Expert
07/08/2018 - 11:23
Let me restate it if what I understood is correct.
Excelchat Expert
07/08/2018 - 11:24
Basically, the problem here is that the formulas you've created do not capture the "inserted row" and auto-adjusts to capture the "original" reference row. Is this correct? :)
User
07/08/2018 - 11:25
the first bit is correct, what do you mean by 'capture the "original" reference row'?
Excelchat Expert
07/08/2018 - 11:26
So for example, a formula is linked to C10, and the last value is in C9. Since the form "inserts" a new C10 value, the formula auto-adjusts to refer to C11. Is that right? :)
User
07/08/2018 - 11:26
yes exactly
Excelchat Expert
07/08/2018 - 11:27
Nice. Okay, the only solution to this is through the use of INDIRECT function and a helper column. It would be great if you can share the google sheets you're working on. Otherwise, I can demonstrate through sample data in the document preview how this can be done.
User
07/08/2018 - 11:28
can you demonstrate here, so I can learn for myself how to properly do it?
Excelchat Expert
07/08/2018 - 11:28
Sure thing. If you look at the document preview, we have source and main sheets.
Excelchat Expert
07/08/2018 - 11:29
Basically, column B of main just "copies" the column A of source.
User
07/08/2018 - 11:29
got it
Excelchat Expert
07/08/2018 - 11:29
Now, if we insert a "new" row 5, the value of that new row 5 is not captured.
Excelchat Expert
07/08/2018 - 11:30
Let me create a column helper and link this with an INDIRECT function.
Excelchat Expert
07/08/2018 - 11:30
Look at column A of main.
Excelchat Expert
07/08/2018 - 11:31
These are merely A1 to A51 values.
Excelchat Expert
07/08/2018 - 11:32
Now look at column B, with the formula =indirect("Source!"&A2)
Excelchat Expert
07/08/2018 - 11:32
Now, let's try inserting a new row 7.
Excelchat Expert
07/08/2018 - 11:33
It will be copied in the column B of Main. That's how we use INDIRECT function,
Excelchat Expert
07/08/2018 - 11:33
basically, column A (our helper column) consists of the cell locations of column A of source sheet.
User
07/08/2018 - 11:34
ahh I see
Excelchat Expert
07/08/2018 - 11:34
Nice. :)
Excelchat Expert
07/08/2018 - 11:34
It's the INDIRECT function that utilizes these A2-A50 as reference.
Excelchat Expert
07/08/2018 - 11:35
So =indirect("Source!"&A2) will always translate to =Source!A2, even if you add new A2 unlimitedly.
User
07/08/2018 - 11:35
that makes perfect sense
Excelchat Expert
07/08/2018 - 11:35
you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
Excelchat Expert
07/08/2018 - 11:35
Do you have any more clarifications with the provided solution? :)
User
07/08/2018 - 11:35
nope thank you very much
Excelchat Expert
07/08/2018 - 11:35
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User
07/08/2018 - 11:36
Cheers!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
