Excel - IF Function Problem - Expert Solution

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

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc