linking tables on Excel for Mac

msampson

Board Regular
Joined
Mar 9, 2004
Messages
129
Office Version
  1. 365
Platform
  1. MacOS
I'm trying to get around the issue of not being able to protect a worksheet with hidden and locked cells but still allow the table to grow as the user adds rows (as soon as you button it up the table no longer expands).

I'm trying to put a table on sheet1 where the user enters data (columns A-N) and receives their calculated results (columns O-Z). Those results come from a table on sheet2 which I'm hoping to hide and lock. The unlocked sheet will then just say =Sheet2!N1 rather than show the formula.
Problem is, how do I link table2 with the calculations to table1 with the inputs so that table2 grows at the same time table1 grows? I'm running a MacBook Pro and I don't seem to have the same features available in Windows.
I can set it up with several thousand rows copied down with formulas that start out with =if(Sheet1!A1-"","",Sheet1!A1) and then all the formulas, but that makes the workbook huge in file size (not emailable), plus it will break if they add more rows than I set up ahead of time.
Maureenn
 
The alternate thing that could happen is that when the source table adds a row, the target table adds a row and copies the previous row to the new one. There are already formulas in A-N that refer to the source table.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Change these two lines...
VBA Code:
        Set SourceRange = Intersect(SourceTable.ListRows(SourceRow.Index).Range, SourceSheet.Range("O:AK"))
        Set TargetRange = Intersect(TargetTable.ListRows(SourceRow.Index).Range, TargetSheet.Range("O:AK"))

... to this ...

VBA Code:
        Set SourceRange = Intersect(SourceTable.ListRows(SourceRow.Index).Range, SourceSheet.Range("A:N"))
        Set TargetRange = Intersect(TargetTable.ListRows(SourceRow.Index).Range, TargetSheet.Range("A:N"))
 
Upvote 0
Didn't see your last comment. Yeah, you could do that. This kind of does that, just with every change in the Table. You could test for new rows, although you'd probably want to set a custom [get] property to see how many rows are in the Table, since there's no event for Tables, only worksheets.
 
Upvote 0
when I changed those two lines, every time I put something in a cell it disappeared after a second and the zeros remained in the other columns. Nothing appeared in the target table
 
Upvote 0
I'm not sure I understand. It takes columns A:N and puts them in columns A:N, like you said. If they're incorrect, change the ranges to what they should be.
 
Upvote 0
it didn't do that. Nothing ended up on the target table and the things from the source table disappeared.
 
Upvote 0
I think maybe the difference is in our terminology. When I set the SourceTable variable, that is where the data originates, the input Table, and the TargetTable is the hidden one with the calculations where the data goes to before it comes back. Are we talking about the same thing?
 
Upvote 0
yes. When I went in an deleted empty rows of table on the hidden target so that I could properly test if it adds a row I got an error message. Runtime error '9' Subscript out of range. It highlighted the second row that you gave me for the substitution. That table doesn't seem to expand when the source table does.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top