Importing Cell Ranges from multiple sheets in the same workbook

[ultracyclist]

I have a spreadsheet with 40 tabs that contain the same column headers and range of cell data (A1:L1500). I want to create a formula on the Summary tab in cell A1 that pulls in the data from all tabs within this range. I tried using the Get Data function and also tried grouping the tabs in a formula to pull everything over, but cannot get it to work. Does anyone know if its possible to create a formula or some other method, i.e. VBA that will accomplish what I'm trying to do.

Thanks,
Tom

 

[My Aswer Is This]

You said:
I want to create a formula on the Summary tab in cell A1 that pulls in the data from all tabs within this range (A1:L1500)

You want all this data from 40 tabs entered into Range A1
I think that would be impossible.

 

[ultracyclist]

Is there not a way to pull the same range of cells from multiple tabs into the Summary sheet, but append the data from each tab so I do not overwrite information from the previous tab? For example, pull A2: L50 on Sheet2 and post in the Summary tab in the same range. Next, it will pull the same range of cells from Sheet 3 and insert the information into A51: L100 on the Summary tab.

 

[Fluff]

If the sheets are consecutive you could use

=VSTACK(Sheet1:Sheet6!A1:L50)

 

[ultracyclist]

I tried VSTACK formula, but its returning a #NAME error. I checked the formula to ensure it matches the Tab names and the range. Thanks for the suggestion

 

[My Aswer Is This]

I also received the same Error.

#NAME

 

[Fluff]

I tried VSTACK formula, but its returning a #NAME error.

Are you using xl365 as per your profile? If so you do not have the latest updates.

 

[alansidman]

another option