Formulas that reference the entire cell data set of a different sheet

[goodysgotacuda]

Alrighty, this one is a bit over my head and I am looking for some help!

• I have an Excel document that has a page of, say, 50 equations on Sheet 2. Within those are some dSum, dAverage, etc formulas that reference data on Sheet 1.
• Sheet 1 will be blank, until I copy and paste data into it. That data can go from Columns A through ~X, and be as long as 100 rows or 200,000 rows.
• I would like the formulas on Sheet 2 to automatically pickup how much data I paste into Sheet 1. So their reference will look something like A1:X198,242 or A1:X2999, depending on how much data I paste into Sheet 1.

The reason I need this is due to using dSum, dAverage, dCount, etc formulas that have to reference the entire data set. I suppose that I could just make those formulas reference from "A1:ZZ100000000" or something like that, but it seems as it's the imporper way to do that.

I tried to make a cell on Sheet 2 that would look at the Data on Sheet one and give me the last populated cell on that Sheet, then those formulas would just reference that cell...but I can't seem to get that done successfully.

Any help?

 

[goodysgotacuda]

As for now, on sheet two, I have this

=ADDRESS(MATCH(LOOKUP(10^10,'Collected Data'!A:A),'Collected Data'!A:A,0),1)

That gives me the address of the last popualted cell within Column A, which is good enough. I have that in cell B1 on sheet two.

Currently, this formula does what I need it to do with my "example data" on sheet 1.

=[@[EngFuelRate '[Gal/hr']]]*(DCOUNT('Collected Data'!$A$2:$X$131082,Calculations!$E$6,Calculations!D17:F18)/3600)

Ideally, I could do this and get away with it, but I cannot get Excel to work that way by guessing high on the column count and selecting the cells from right to left, instead of the ordinary left to right.

=[@[EngFuelRate '[Gal/hr']]]*(DCOUNT('Collected Data'!$CC$2:Calculations!B1,Calculations!$E$6,Calculations!D17:F18)/3600)

Likely I just have a syntax error, need a "&" or ' ' in there somewhere to properly through that into a cell range...but I cannot figure out how to do that!