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

 

[msampson]

I just combined input and results to keep things together so that the user interacts with one table only.
At this point it's just Table2 on Sheet1 starting in A4 and the calculations are performed on Table1 on a sheet called "calc" with A4 coming in from Sheet1A4
Sheet1 has inputs in columns A-N
calc has A-N coming from Table2 and calculations performed in columns O-CG
Table2 also has columns O-AK where I pasted results from calc.
There is also a graph with data that comes from calc.

 

[Zack Barresse]

Perhaps I'm confused then. What is it exactly you're needing to do? I thought I might get there from the answers to my questions but I'm still not following what you actually want done.

 

[msampson]

on sheet1 the user inputs data in columns A-N.
I want that data to populate a table on another sheet "calc" so that the table on calc expands as the original table expands. The table on calc has a ton of columns that crunch the input (O-CG).
Some of the results from columns on calc are then sent back to sheet1.
This way I can hopefully hide calc and the user will see what they put in and then in the result columns the formula they see will simply be =calc!"somecell"

 

[Zack Barresse]

Some of the results from columns on calc are then sent back to sheet1

I'd like to expound on this. More details are needed, as we do not know what "some" refers to, nor "results", nor where these columns would link. It would again help to answer my questions.

 

[msampson]

the table on sheet calc is very similar to the table on sheet1 so that the information that ends up in columns O-AK get transferred back to sheet1 in the same columns (O-AK). There are columns AL-CG on the sheet calc that do all the formulas with the inputs from columns A-N and O-AK reference all the crunching done in AL-CG.
I could maybe send it to you if that would work.

 

[msampson]

By having a second sheet I'm trying to prevent columns AL-CG from having to be visible, but that is where all the formulas crunch the inputs. I would also hopefully be able to lock and hide the second sheet

 

[Zack Barresse]

Are the source Table columns ("sheet1"), which are to receive the updated formula values, named the same as the columns on the target Table ("sheet calc")? It would be easiest to use Table column names as opposed to worksheet column letters.

 

[msampson]

Yes they are

 

[Zack Barresse]

Ok, well, you could always adapt the below code for it, but I coded it up without that in mind, so it's referencing the worksheet columns. Please note this can fail if the Table moves in any way. There is no error handling on this at all.

Make sure you set the Worksheet and Table names accordingly. This goes into the source sheet's code module. Please note the following definitions (change as desired):

• Source : Sheet where data input is done
• Target : Sheet where calculations are derived

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim SourceTable As ListObject
    Dim TargetTable As ListObject
    Dim SourceRow As ListRow
    Dim SourceRange As Range
    Dim TargetRange As Range
    Dim EventsState As Boolean
    
    Const SourcePassword As String = "password"
    EventsState = Application.EnableEvents
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Source : Sheet where data input is done
    ' Target : Sheet where calculations are derived
    '
    Set SourceSheet = Worksheets("Sheet1") '<< Name accordingly
    Set TargetSheet = Worksheets("sheet calc") '<< Name accordingly
    Set SourceTable = SourceSheet.ListObjects("tSource") '<< Name accordingly
    Set TargetTable = TargetSheet.ListObjects("tTarget") '<< Name accordingly
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    If Not Intersect(Target, SourceTable.DataBodyRange) Is Nothing Then
        
        If Application.Calculation <> xlCalculationAutomatic Then
            Application.Calculate
        End If
        Application.EnableEvents = False
        
        Set SourceRow = SourceTable.ListRows(Target.Row - SourceTable.ListRows(1).Range.Row + 1)
        
        If SourceSheet.ProtectContents Then SourceSheet.Unprotect SourcePassword
        
        Set SourceRange = Intersect(SourceTable.ListRows(SourceRow.Index).Range, SourceSheet.Range("O:AK"))
        Set TargetRange = Intersect(TargetTable.ListRows(SourceRow.Index).Range, TargetSheet.Range("O:AK"))
        
        SourceRange.Value = TargetRange.Value
        
        If SourceSheet.ProtectContents Then SourceSheet.Protect SourcePassword
        Application.EnableEvents = EventsState
        
    End If
    
End Sub

 

[msampson]

Thank you. Part way there I think...

it brings zeros to the front sheet. It needs to take the input data in columns A-N from the source sheet and stick it into A-N on the target sheet in order for calculations to happen.