Formula not working in a newly inserted row

This is my formula

=IFNA((VLOOKUP(D2,ProductCost,2,FALSE)*E2)+VLOOKUP(C2,ServiceCost,2,FALSE)-VLOOKUP(C2,ServiceCost,2,FALSE)*VLOOKUP(F2,Discount,2,FALSE)+G2,0)

It works perfectly the way I want it to.

I can copy the formula into the next row and the formula continues to work properly.

BUT once I insert a new row (There are cases where I need to insert a row between 2 rows and not just add a row) the formula does not work. The formula appears to copy properly including changing the row numbers correctly but it doesn't actually make the calculation!

What is going on???

Sounds as though Calculation is set to Manual.


Regards, TMS

On the newly inserted rows I can clear every aspect of the cell, manually type in the correct formula and it still will not work.....

Just checked the calculation mode, it's set to automatic.

When you say "does not work" do you mean that the result is an error or something like 0 when you expect a different value?

Outside of the calculation being set to manual as suggested by Trevor (I think that he is most likely correct), are there values for the inserted rows to pick up in columns C to G? You are not clear when you say that you are inserting rows if those columns also have inserted rows at the same time. If that is the case and there are no values in the inserted rows in columns C to G the references would fail when addressing new rows inserted in those columns unless they were populated with appropriate values. If the formula when copied into the new rows actually points to values in the columns C to G then I think that Trevor is correct.

Pictures paint a story but there's not a lot we can do with them to help diagnose the problem.

Can you post a sample workbook with any sensitive information removed.

Regards, TMS

I removed the sheets that contained my banking info, but those did not have anything to do with the sheet 'Income'

The values being looked up with the VLOOKUP was returning a #N/A. Rather than have a blank lookup value in the lookup range, eliminate that and amend the VLOOKUP function to include an error trap which returns 0. I deleted the blank values at the top of each of your named ranges then included the error trap. Now, a new row can be inserted and the formula filled down without problem.

Formula:

Please Login or Register  to view this content.

@newdoverman: good spot. Took a while to cotton on to that myself (even after you spelt it out)

Slightly shorter formula:

Formula:

Please Login or Register  to view this content.

@Brenda: is there a specific reason you seem to force 8 pages? That seems to go against the fact that a) you add rows at the bottom and b) you want/need to insert rows.

All things being equal, I'd recommend that you convert the data to a Structured Tables. That will maintain the formula, formatting, Conditional Formatting and Data Validation. Incidentally, given that you have tables set up, it would make sense to use them for selecting Service, Product and Discount.

Regards, TMS

@TMS
I starred at that for a long time before I had the "ah crap" moment, why didn't I think of that sooner!

Hi Brenda

I would recommend a) don't have separate sheets for each of the therapists and b) have a single sheet where you record all the data AND convert that to a Structured Table. Excel manages the ranges in a Table and, as I said before, new rows will automatically carry forward formulae, formatting, Conditional Formatting and Data Validation. They also include in-built filtering and sorting. So, you can, for example, sort the data by date AND filter out one or more of the therapists for printing, etc.

Oh, and the reason for having one sheet: one day you will want/need to compare, contrast and analyse the data. In one sheet, it's easy. In lots of sheeted it's a PitA

Regards, TMS

Here is an example of using a table for your data input on the Income worksheet and on the Summary worksheet, an example Pivot Table using the data from the table on the Income worksheet.

You're welcome. Thank you for the feedback.

Remember TMS' suggestion in message 15

"Oh, and the reason for having one sheet: one day you will want/need to compare, contrast and analyse the data. In one sheet, it's easy. In lots of sheeted it's a PitA "