0

Thank you for reading my question.

I am looking to replicate certain columns (e.g columns A,D,F) from a table (e.g TableX) DYNAMICALLY to a set of columns (e.g columns H, J, K) in a second table (Table Y) in a second workbook (Workbook 2).

I want to make sure the link is live between the two, and that if I delete or add rows to Table 1, Table 2 automatically expands/retracts to represent the same number of entries.

I have tried linking the tables, but I end up with !REF errors. I have tried Linking Named Ranges, but I seem to lose expansibility. I have tried linking Table 1 to an Access database, then Linking that Access database with Table 2, but have not been able to get it to work.

Any thoughts?

Cheers for any help. R

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
RDawn
  • 1
  • 1
  • Use Microsot Query to link the source Workbook to final Workbook. That way, if any data is modified in source, you just need to refresh the second workbook to get the updated data. – Foxfire And Burns And Burns Aug 16 '18 at 07:01

1 Answers1

1

If the Row Number of Table 1 in Excel Workbook 1 and Table 2 in the Excel Workbook 2 are the same, (Example, both tables begin at row number 5), then use the following formula

=IFERROR(IF(ISBLANK('Excel Workbook 1.xlsx'!Table1[[#This Row],[Col_A]]),"",'Excel Workbook 1.xlsx'!Table1[[#This Row],[Col_A]]),"")

If the row numbers of Table 1 and Table 2 are different, (Example Table 1 begin at row 8 and Table 2 begins at Row 25) then use the below formula.

=IF(ISBLANK(OFFSET('Excel Workbook 1.xlsx'!Table1[#Data],ROW()-ROW(D$4)-1,0,1,1)),"",OFFSET('Excel Workbook 1.xlsx'!Table1[#Data],ROW()-ROW(D$4)-1,0,1,1))

In the 2nd formula, the D4 is the location of the title of the column of Table 2.

In the 2nd Formula, I am assuming that there is no data below Table 1. All rows below the title of Table 1 should only contain data which is a part of Table 1.

Change the Cell References, Workbook Names, Sheet Names and Table Names as required.

I hope this solves your problem.

Regards,

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

Vijaykumar Shetye
  • 44
  • 2