Having trouble sharing Excel tables with other workbooks?

Unfortunately, Shared Workbooks don't support Tables, so you'd need to convert them to ranges to share.

As for XML, see: Overview of XML in Excel - Excel - Office.com

Note that Shared workbooks suck and should generally be avoided if at all possible.

Thanks for your quick response.

If I can't share Excel tables, is there another way of Excel automatically adding my formulas to a new row when inserted?

Matt

You can use VBA; most likely a change event after a specific cell has changed.

If you can specify what cell should trigger the formula copy, and what range to copy, someone can write up the code for you.

This is my table I am using:

The formulas in cell H, J, K and M (shown as #DIV/0!) need to be the same in each new row when adding a new measurements into the table, if you can understand what I mean? otherwise I have to insert a new row and copy and past them in every time which is a pain.

So I take it that you're trying to set this up for multiple people to be able to update? If so you might want to look at the Excel Web App, which I believe does support tables.

Otherwise, here's some Change event code that will fire after Weight has been entered:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C:C")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Range("D2:I2").Copy Target.Offset(, 1)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Thanks for your response, but I am having trouble understanding how and where I input the data to get this to work. I am quite new to Excel so my knowledge is limited at present. Would I have to add a new column to input the change code data for the cells I want it to copy to a new row if I insert one?

Right-click on the worksheet tab and select View Code. Then paste the code in the new window that opens on the right. ALT+Q will exit you back to Excel to test it.

When data is entered into the weight column, the formulas in D1:I1 will be copied to that row.

Note that the VBA project will be locked as soon as you share the workbook, and while the code will function it can't be edited.

I have tried entering the code you gave me in view code, but nothing happens. I am more than likely doing it wrong? I cut and past the text you sent as a hole. when I inserted a new row it inserts it above but the formulas have not copied even if I enter data in the weight column nothing happens. Like I said I am probably doing it wrong.

I copied this into the view code:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C:C")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
Range("D2:I2").Copy Target.Offset(, 1)
End Sub

The cells I am needing to copy to the new Row when inserted, are H1 (BMI), J1 (BMI Catogory), K1 (Over All Health Risk) and M1 (review Date).

Sorry to be a pain, but I am struggling to understand. Thanks for your continued help it is most appreciated.

Open the VBE (Visual Basic Editor) (ALT+F11 will do it) and look for the code you pasted in. It should be in the same worksheet module as the worksheet where you need it to work. Then you can goto this line:

Range("D2:I2").Copy Target.Offset(, 1)

and hit F9, which puts a breakpoint on that lie and the code will stop when it gets there. Then in your worksheet made an entry in Column C and see if that line of code is highlighted in yellow. If it is, then you can hit F8 to complete it. If it doesn't then the code is most likely in the wrong place.