linking tables on Excel for Mac

[msampson]

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

 

[Zack Barresse]

Hi there,

What exactly is the reason for locking the input Table? Generally, the solution to this bug (yes, it is a bug, I don't care what Microsoft says about it being "as-intended") is to handle it downstream as opposed to upstream, i.e. transform your data from the Table as the source.

 

[msampson]

mainly to protect it from breaking since I'll be giving the sheet to others to fill in. But there is also a bunch of extra columns generated to crunch and analyze the data and it would be best if all that is hidden away.
I have done a smaller version of this using one single table where I hide all the extra columns and use data validation to prevent formula cells from changing, but this version is more complex so if I can link a second table it would be easier for all that to be done by the "wizard behind the curtain".

 

[Zack Barresse]

I would advise to set your Table, as much as you can, with just your input columns and add the others with Power Query (Data tab, Get & Transform group, From Table/Range button). Sometimes this might not be possible, I understand that. If this is one of those times, I would use VBA to update/push data to where you want. You do not want to do this with every change in the Table. That would get resource intensive. You want to do as much as you can without adding more data. Also, if you're going to be updating a data source downstream from the input Table, you also might want to think about making the values static after you copy/move/add/whatever to them. Power Query is by far a better solution though.

 

[msampson]

I don't believe Power Query is available on the Mac

 

[Zack Barresse]

Are you on Office 365 for Mac? Look on the Data tab for the 'From Table/Range' button.

 

[Zack Barresse]

See where your version is here: Where is Get & Transform (Power Query)

 

[msampson]

From HTML is the only one not greyed out. From Text and New Database Query are there but grey.

 

[msampson]

I have Office 365 for Mac version 16.36

 

[Zack Barresse]

Oof, ok. Rip. Can they use the input Table without the calculated columns? I'm assuming you need them, not for input but rather downstream.

What is the input Table name and the worksheet name it resides on?

Where sheet is the output data located on?

What are the calculated column formulas?

When should this downstream Table/range update?