Linking two different Excel tables and auto-populate one when populate another

Question

I have two diferent excel formatted tables one near another. First table (green headers) is a table where I have to add some chemical formulas in Column A and Column B will be SUM of all compounds that I will add from second table (yellow headers), which represents Periodic System of Elements!

The formula that I am using in Table 2 (yellow headers) for calculating chemical compounds is this:

=C$2*MAX(IFERROR(IF(FIND(C$1&ROW($1:$99);MolM.[@[Mol. Formula]]);ROW($1:$99);0);0);IFERROR(IF(FIND(C$1&CHAR(ROW($65:$90));MolM.[@[Mol. Formula]]&"Z");1;0);0)) (CSE formula)

What and how I am usually doing this update of new compounds is that I am adding new chemical formulas in Column A manually (that is okay) and then dragging main formula in Table 2 (yellow header) to calculate all elements, and then SUM in column B for the main result!

My question is, is there a possibility to be more automated, just when I type new compound in Column A it will expand as normal table do, but also to auto-expand and calculate rest of compounds, without that I drag the formula manually..?

Hopefully this was clear enough.

Is there any possibility to make this happen? Is the only solution Power Query or?

It's a little hard to figure out exactly what you are trying to do (or rather how), but pretty much anything can be done with VBA. It might even be possible with a worksheet function. — Christofer Weber, Mar 15 '21 at 11:35
Because there is no event that gets triggered if a new row is inserted the only way I see is having a button that adds the row in both tables or a button that copies from table one to table two. • Your question is a bit too broad to give a more precise answer. — Pᴇʜ, Mar 15 '21 at 12:26
@Pᴇʜ Yeah I was also thinking about, but I should do it without VBA, because of sharing. — MmVv, Mar 15 '21 at 12:45
@ChristoferWeber It is pretty simple, I would like to connect two almost different tables, to give me the result of using both. But I think this is for VBA. — MmVv, Mar 15 '21 at 12:47
@Pᴇʜ But wait a sec, I got actually some formula in second table (yellow headers), where I calculated molar masses pro/molecule (compound). Can it be helpful as a trigger? — MmVv, Mar 15 '21 at 12:51
@Mirza can you please give a [mcve] of your problem with full example input data and desired output (where you tell us which parts of data are fix data and which part should be generated by formula/vba)? The thing is I can't tell what you wan't with the description you gave. Can you be more precise? — Pᴇʜ, Mar 15 '21 at 12:53
@Pᴇʜ So, I did complete explanation...hopefully is a bit better :=) — MmVv, Mar 15 '21 at 13:03
You might be interested in the answer I gave [here](https://stackoverflow.com/a/46091904/3219613) you could use that in combination with a worksheet change event that triggers if you write a formula in column A and deploys the function of the link in the other columns. — Pᴇʜ, Mar 15 '21 at 13:31

Christofer Weber · Accepted Answer · 2021-03-15T13:36:43.130

0

I'm making a wild guess here. Say that you write NH3 in A3, and then have it print I2 (value of "N") in I3, and C2 * 3 (value of "H" times 3, "H3") in C3. To then have B3 calculate the total value with =SUM() or similar.

You could have a VBA sub that looks for the value and prints this. Here is a prototype of that:

Sub molFunc(chem As String, formRow As Long)
Dim i As Long, c As String, atoms As Range, a as Range

Set atoms = Range("C1", Cells(1, Columns.count).End(xlToLeft))    
For i = 1 To Len(chem)

    If Not Mid(chem, i + 1, 1) = UCase(Mid(chem, i + 1, 1)) Then 
        c = Mid(chem, i, 2)
        i = i + 1
    Else
        c = Mid(chem, i, 1)
    End If
    
    For Each a In atoms
        If a.Value = c Then
            If IsNumeric(Mid(chem, i + 1, 1)) Then
                a.Offset(formRow - 1).Value = a.Offset(1) * Mid(chem, i + 1, 1)
            Else
                a.Offset(formRow - 1).Value = a.Offset(1)
            End If
        End If
    Next a
Next i
End Sub

Then you can call it from a Worksheet_Change event in the worksheet of your choice.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing And Target.count = 1 Then
        Application.EnableEvents = False
        Call molFunc(Target.Value, Target.Row)
        Application.EnableEvents = True
    End If
End Sub

edited Mar 15 '21 at 13:36

answered Mar 15 '21 at 12:59

Christofer Weber

1,464
1
9
18

If you modify a value of a cell `a.Offset(formRow - 1).Value =` starting from an `Worksheet_Change` event you should turn off `Application.EnableEvents` or you easily get an event loop running. – Pᴇʜ Mar 15 '21 at 13:07
Well there "Shouldn't" be any loops, thanks to the Intersect check, and the macro should never touch `A`. But still, yes. There should also be a check to prevent errors in the sheet when deleting large selections and such. – Christofer Weber Mar 15 '21 at 13:15
@ChristoferWeber Yea man! Nice gif, exactly what I would like to have! Will test it out, I will write my report! – MmVv Mar 15 '21 at 13:17
@Mirza If you have the formula already, this might be quite overkill, just have it copy the formula down one row would be enough. – Christofer Weber Mar 15 '21 at 13:20
@ChristoferWeber But dont forget you got 118 elements, and I have to firstly scroll to the last Column and then copy it down... it is overkill :D – MmVv Mar 15 '21 at 13:22
@ChristoferWeber I think you are missing something in formula, like , a As String to define... It gives me an error – MmVv Mar 15 '21 at 13:25
I mean that my macro replaces your formula, instead of just copying it, which would be simpler. Do you have both subs? What is the error? – Christofer Weber Mar 15 '21 at 13:29
@ChristoferWeber Variable not defined -> For each a In atoms – MmVv Mar 15 '21 at 13:35
@Mirza Sorry about that, didn't have exlpicit on. Just `dim a as range` (updated the code) – Christofer Weber Mar 15 '21 at 13:37
@ChristoferWeber Now happens nothing. No error, nothing. I typed some compound and it is only 0,00 in Column B, without any calculations on second table – MmVv Mar 15 '21 at 13:49
@Mirza Try saving, closing and opening the file again. Worksheet macros have glitched out like that a lot of times for me. – Christofer Weber Mar 15 '21 at 13:56
@ChristoferWeber Allright!! This works now! Thanks a lot! – MmVv Mar 15 '21 at 14:01

Linking two different Excel tables and auto-populate one when populate another

1 Answers1