One formula for lots of data

[BeerBeer101]

alright, I've never done this one - but I'm sure there's an easy formula versus thousands of the same one.

Source sheet: updates weekly - it's colums A-F wide - data after headers starts row 3 and is anywhere from 4,000 to 5500 rows long
target sheet - starts A2

so there's is there a formula for A2 to bring the source data into the target sheet per cell (as opposed to =sourcesheeta2 copied right and down for let's say 30,000 formulas?
thanks - this group is always amazing!

 

[BeerBeer101]

In that case clear all the cells in cols A:F from row 2 down & put the formula back in A2 only

OK... one more hitch (noted above - but not clear)
the target sheet is a table - so i still get the #SPILL!. when I do it on a plain sheet - no problem - but i need this as a table. further thoughts? thanks so far...

 

[Fluff]

You cannot use spill ranges in a table. Either use it in a normal range (IMO the best option) or use a separate formula in each & every cell.

 

[BeerBeer101]

You cannot use spill ranges in a table. Either use it in a normal range (IMO the best option) or use a separate formula in each & every cell.

OK, so it won't work in a table (I need to table to further extract the data to many other tabs within the sheet. using a seperate formula makes the filesize of the sheet larger - but gets the job done

 

[Fluff]

You can extract the data to other tabs regardless of whether it's in a table or not.

 

[BeerBeer101]

You can extract the data to other tabs regardless of whether it's in a table or not.

I understand that - but that would mean rewriting the rest of the sheet for this one quick fix... so i'll leave it at that.