How to link worksheets in excel to allow inserting new rows

2
votes

I have a master sheet which is made up of data from 5 sub sheets. The data is updated in the sub sheets and then linked to the master sheet.
I want to be able to add new data (rows) to the sub sheets and have the master sheet update automatically. I used the INDIRECT function, but it requires me to estimate the max number of rows and then copy the function that far. This leaves my master sheet with large white spaces where the link switches between sub sheets. Ideally I would like to be able to have the master sheet be continuous with no white space in the middle of the data.

This is the function I used:

=OFFSET(INDIRECT("'Sheet1'!A2"),ROWS($A$2:A2)-1,COLUMNS($A$2:A2)-1)

Note:
My data starts in row 2 because row 1 has heading data.

1
excelexcel-formula
You can use ISBLANK to evaluate if a cell is empty, but that would require making a very complex formula with a number of nested IF statements. Have you considered using VBA to move the data? – thunderblaster
My VBA knowledge is nonexistent, so I was hoping not to have to use it...I'll look into ISBLANK and see if I can come up with something not too complicated. – Brandon West
You could try a pivot table from multiple ranges blog.contextures.com/archives/2010/04/16/… otherwise I think you'll need VBA. – Dick Kusleika
Tables in Excel can Help (by generating automatically columns with formula), but for resizing them you need VBA (for new adding rows). – shA.t

1 Answers

0
votes

little confused as to what you are trying to do exactly but

using the count function so you don't have to estimate the rows? 

=OFFSET(INDIRECT("'Sheet1'!A2"),0,0,Count($:A),Count(A:A))

also what's in cell sheet1 A2 that you need indirect? is it just to point to the name reference for the subsheets? which if is the case then i assume all sub-sheets are formatted the same?

 =OFFSET(INDIRECT("Sheetname"&"!A1"),0,0,count(indirect("sheetname"&"!A:A")),count(indirect("sheetname"&"!1:1")))

again i'm not exactly clear as to what you are trying to accomplish