if I am trying to pull data from multiple workbooks to be added as rows, how can I get Excel to auto-insert the amount of rows, rather than referencing individual cells?

i.e. if the number of rows in a workbook being referenced fluctuates, I can’t simply reference cell-to-cell…

i.e. If cell A5 is set to pull in and list all data from Workbook 1, which is 50 rows - cell A6 is set to pull in and list 100 rows from Workbook 2, and A7 set to pull in 50 rows from Workbook 3 - so it all lines up nicely as 500 rows when complete…?

What function/formula would this be - to have Excel auto-calculate how many rows are to be pulled in from the referenced worksheet, which also auto-inserting that many rows as it’s pulling in the data?

thanks!!

UPDATE:

Dear community members,

I am new to this community, and didn’t get off to a great start by not clarifying the community rules and making the mistake of crossposting 3 questions in a few other forums.

It was quickly brought to my attention that crossposting is a no-no - and to make up for the error, I immediately updated my posts with an apology and crosslinks to the other posted questions, as per recommendation. I do now understand why the rule against crossposting exists - and I would like to ensure everyone it was never my intention to disrespect anyone’s time or expertise in any way. Ignorance may not be an excuse for my breach of your cultural courtesy - though as I do highly value integrity, I would like to ensure to make up for my error.

As per another recommendation, I’d also like to offer another sincere apology and ask your forgiveness. I do wish to remain in good standing with your community and honor the time and effort you put into serving others through your sharing. And as such, I would like to clarify that to ensure such an error will not occur again, I’ll be limiting my activity to the VBA Express and Excel Forum, ensuring posting one question in only one forum at a time - and if they don’t get answered and I post elsewhere, will ensure to include crossposts in both.

As such, you may disregard the questions from this original post, as I cannot delete it. The thread has carried on into more detail at http://www.vbaexpress.com/forum/showthread.php?54696-Absolute-Relative-W...

Again, apologies for the disrespect I ignorantly displayed by posting before being clear on the forum rules.

Thank you for your understanding, patience, and forgiveness.

Regards...