Google Sheets - IF statement with INDEX arguments giving me #ERROR (Parse Error)-CodePudding

To give some detail, I am using Google Forms Google Sheets to make an easy database that collects all the bank information I put in (Income, savings, expenses, etc.) and I can keep track of details with easy sorting.

I'm stuck on a formula that I can't seem to figure out. Before I go in to specific details of my code I will give a small explanation. The cell I am trying to populate [with the formula] is supposed to take 2 pieces of information from the form response sheet (I will refer to it as 'rawdata') and produce a value (number) from another cell in 'rawdata'.

So in 'rawdata' I have a column (B) that has a value of "Expense" or "Income" for a form response line. I have a column (D) in 'rawdata' that has the value of "Amount" for the number submitted.

Cells in reference in 'rawdata':

B9 = "Expense"

D9 = 67.37

Now, due to the original 'rawdata' being a form response sheet it inserts new rows every response, so my attempt at just calling direction to the cell fails upon a new response submission because it increments on its own. So that being a solution is out of the question.

So, for every other cell I am using to display values from 'rawdata' in an organized sheet I use =INDEX() to pull the value from that exact row and column, instead of the cell reference and it has worked so far.

=INDEX(rawdata!$B:$B,ROW(9:9)) displays "Expense" from 'rawdata' in B9 as a result. So the rest of the cells are formulated this way and I have had no issues.

When I got to the amount, I used an =IF() formula to pull the value from the D column and to display it either as a negative or positive depending on what column B states.

=IF(rawdata!B8="Expense", -rawdata!$D9, rawdata!$D9) displays -67.37 as a result.

But since I can't use this formula to fill empty cells for future use I have to find another way to do so. I figure;

=IF(=INDEX(rawdata!$B:$B,ROW(9:9)) = "Expense", =INDEX(-rawdata!$D:$D,ROW(9:9)), =INDEX(rawdata!$D:$D,ROW(9:9)))

is my solution to the problem, because to me it makes sense. But it fails and gives me a Parse Error.

So when I try to run the first =INDEX() with the values of 1 or 0 being the outcome of the =IF() it also fails, so I create a new column in the sorted table to pull the data of "Expense" or "Income" so I can reference that.

Cell in 'processeddata':

E9 = "Expense"

=IF(E9="Expense", 1, 0) displays 1 as its result, so I figure this is the solution to my problem.

=IF(E9="Expense", =INDEX(-rawdata!$D:$D,ROW(9:9)), =INDEX(rawdata!$D:$D,ROW(9:9)))

displays #ERROR with the description Parse Error.

=INDEX(-rawdata!$D:$D,ROW(9:9)) displays -67.37

=INDEX(rawdata!$D:$D,ROW(9:9)) displays 67.37

I'm at a loss for what to do now. I've exhausted myself with tons of searching on here and all over google, tried using things like INDEX and MATCH, VLOOKUP, etc but can't solve this to save my life. Does anyone here have a clue how I can solve this with a cell reference that won't increment when new rows are added in 'rawdata'?

PS. I've also attempted using =VALUE() to try and convert the =INDEX() to a number but no luck.

CodePudding user response：

Your formula has superfluous equals signs (=). Try Insert > Column and this formula in row 8 of the new column:

=arrayformula( if(rawdata!B8:B = "Expense", -rawdata!D8:D, rawdata!D8:D) )

CodePudding user response：

Your formula has some equal signs in it that are probably the issue.

Instead of this:

IF(=INDEX(rawdata!$B:$B,ROW(9:9)) = "Expense", =INDEX(-rawdata!$D:$D,ROW(9:9)), =INDEX(rawdata!$D:$D,ROW(9:9)))

try this:

IF(INDEX(rawdata!$B:$B,ROW(9:9)) = "Expense", INDEX(-rawdata!$D:$D,ROW(9:9)), INDEX(rawdata!$D:$D,ROW(9:9)))

Also, an array formula is a great tool for Form data calculations. In this file you can see a formula in Column F that does a calculation in each row of the responses sheet from this form, it will continue to update as new entries are made. Feel free to enter submit some form responses.