#REF! error when inserting row into table

[preciousmetals]

Getting a #REF! error when inserting additional rows to a table. The #REF! error is related only to the new row that is inserted. I have a data input in another spreadsheet and since I put that in and have a formula running from it the error has been happening. It wont update to the next row even though there are no $ on cell input on formula. I can send spreadsheet to get a better picture just wondering if anyone knew why I have spent weeks trying to figure it out!

[AliGW]

Welcome! Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[preciousmetals]

should be attached hopefully

[AliGW]

I see no #REF! errors at all in the attached workbook.

[preciousmetals]

sorry I have attached the spreadsheet with the #ref! error in question highlighted red

[AliGW]

Does this YouTube tutorial help at all? It shows how to adapt formulae using the OFFSET function to avoid the #REF! error.

https://www.youtube.com/watch?v=L_rRC9plsTU

[peterrc]

Hi,

One way to resolve this is to click on the cell that has the actual #REF! error (example cell L5, Gold Inventory sheet,=#REF!-I5). You can also select the "Range" with the #REF! errors.
Then hold down the control key and press "D" on your keyboard.
This combination copies the formula down from the cell above.

Regards

peterrc

[preciousmetals]

Is there any way of enabling this to happen automatically when inserting new rows without having to press control d? for some reason when I insert a new row column J's formula jumps from =H4*Spot!$I$7 to =#REF!*Spot!$N$2 when it should be jumping to =H5*Spot!$I$7 and next insert to =H6*Spot!$I$7 and so on. I have put this together for the guys on a precious metals forum and most are very limited with excel so I am trying to make it as easy as possible for them

[AliGW]

Did you consider the tutorial on YouTube about using OFFSET? I posted a link above.

[preciousmetals]

I have watched it 5 times I cant quite grasp what he means when he says at the end it can be used in range as well. I don't know that it applies to what I am looking for? I understand the concept when rows are deleted but I am inserting rows as more rows are needed so the range is always being added to. I don't think its the range that is the problem? for some reason the reference to the spot worksheet isn't staying as $I$7 even though nothing is being deleted? and the H4 isn't changing to H5 even though there is no $H$4?? and I just don't see why maybe this one is over my head

[peterrc]

Is there a particular reason why you have to insert rows?
As you Data is a "Table" as you add more rows to the bottom of the "Table" the formulas will automatically copy down.
Please advise.

Regards

peterrc

[preciousmetals]

rows need to be inserted as more metals are purchased. yep that's what I mean I have it set up as a table so it should be auto copying as I insert rows but for some reason its giving me this #REF! error and I cant work out why as nothing is being deleted?

[peterrc]

Hi,

Unfortunately, when you simply insert a new Row in a Table (or a Range) the formulas are are not automatically copied'.

I have attached a "revised" version of your Gold (Inventory) sheet that includes the following:-

The Total row that you had has been deleted
A new Total Row has been added from Table Tools - Design - Total Row has been "ticked"
IFERROR has been added to the formulas so you will not get #REF! error or #DIV/0! error if the related cells are blank.

If you select the "marker" on the bottom right cell (M27 in the attached - do not select the cell) the "cross" changes to an "arrow" and if you drag it down more rows appear with the formulas copied automatically.
Please note the total Row automatically "moves down" to the last row.
If you select any cell in the new Total Row, click the down arrow, and you will have numerous options - Count - Average - Sum etc. etc.

Now you do not have to insert rows - hopefully problem solved.

Please let me know if this works for you.

Regards

peterrc

[preciousmetals]

wow peter! this is exactly what I was looking for. excel is a complex and at times frustrating beast. I thank you for your time the guys on the precious metals forum will be very pleased. I will definitely be keeping the IFERROR function in mind it appears if all else fails IFERROR! haha