Struggling with link between two Excel tables - It works, but it doesn't. : )

Hi all,

I have a source table with headers starting at row 6 and records starting at row 7.

I want to create a secondary table which references the source table, but with headers starting at row 12 and records at row 13. This is because I want to have a few statistics and rows calculated on top of the table.

1. Normally, one can simply enter in cell B13 = Source!B7. This is illustrated in the sheet named "Link1".

This works for most purposes, but my original table is updated using VBA code extracting data from a database. Whenever I do this, this type of reference is destroyed and the cells in Link 1 shows #REF upon refreshing.

2. I found a solution for this by finding the name of the table and entering in A13 = SeriesTable[Date]. This lets me also upload by database/VBA without any trouble at all. Data is updated just fine.

The trouble with this formula is that it references the exact same row in both sheets. So, while I'd want to have the first row of data from Source, Row 7 in Row 13, it gives me Row 13 from Row 13 in Source. So, I miss the first rows of data.

Is there a clever solution for this?

I'm attaching a simple mock-up sheet as referenced above.

Many thanks for all help on this!

Best regards,

Elijah

Hi, MrShorty,

Thank you for your comment. I didn't create the VBA myself, so I can't say for sure, but I imagine it's deleting rows...

Anyway, I managed to solve this using PowerQuery in Excel. Turned out to be quite easy in the end.