MS Excel formula "skips" row based on input from MS Forms

[joakimgg]

Hi, 

I have an Excel Online document which receives survey data directly from a Microsoft Form. 

In the Excel document I have a raw data sheet and a calculation sheet. The calculation sheet is referencing columns and rows from the raw data sheet, and updates dynamically as new data is entered. 

The problem is as follows: Each a new form submission is submitted which creates a new row in my raw data sheet, the formula in my calculation sheet "skips" the newly added row. 

How can I modify my formula so that it is updated with the correct row reference each time a new row is added to my raw data? I assume that I may have to incorporate 'INDEX' into my formula in some way.

Below is the formula used in my calculation sheet: 
=IF(RawData!J7="","",IF(RawData!J7="Agree",4,IF(RawData!J7="Partly agree",3,IF(RawData!J7="Partly disagree",2,IF(RawData!J7="Disagree",1," ")))))

Here's the "raw data":

Any help would be greatly appreciated! 

[#2]

@joakimgg 

Since the RawData is in fact an Excel Table, I think the formula could be rewritten so as to accommodate the size of the table--the number of rows in each column--automatically.  It would do this by referring to the column heading rather than to specific cells.

But maybe we could "back up" and look at the overall process here: are you in the end wanting to get averages of responses to each question. Describe, if you will, the full process, what the goal is. Are all the questions ones with the same four possible responses? Etc.

Finally, is it possible for you to post a copy of the spreadsheet?

[#3]

@mathetes 

Thank you for your response! 

The document is directly connected to my Microsoft Form, since it was created through "forms for excel" in sharepoint.

Every time a new response is submitted, a new row is added to the "RawData" table. What is want to achieve is: making the formula in the separate sheet reference the correct row, without having to "drag" the formula downwards manually. 

The issue is that every time a new row is added automatically, the row is not added in the separate calculation sheet.

All questions have the same possible responses, and I would like to be able to measure averages etc.

The xlsx file type was not supported for uploading a sample, but perhaps there's another way of doing it?

[#4]

@joakimgg 

The xlsx file type was not supported for uploading a sample, but perhaps there's another way of doing it?

I'm sending you a personal message. You should be able to respond to that message directly to me, attaching a copy of your file to that response, sending it back to me that way.

Look for it by clicking on your avatar at the top right of this forum page.

[#5]

Thanks! I've sent a copy of the file to you now.

[#6]

@joakimgg did you ever find a solution to this issue?  I'm having the exact same problem but haven't been able to find anybody that has successfully solved it!  Thanks so much in advance!

[#7]

If my memory serves me correctly, this was solved by discovering an error in the formula that was doing the calculations.

[#8]

@joakimgg Hello, my spreadsheet continues to skip. Was there a resolution provided?

[#9]

@joakimgg @mathetes what was the resolution to this? I am having the same issue :'(

[#10]

@jwhitehousecanyon 

The original was so long ago....I gave you already my best recollection. Might I suggest you'd be better served by starting a new thread with your question and posting your spreadsheet if possible so we could take a look at it.