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

#### goodysgotacuda

##### Board Regular
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?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### goodysgotacuda

##### Board Regular
As for now, on sheet two, I have this

Rich (BB code):
``=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.

Rich (BB code):
``=[@[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.

Rich (BB code):
``=[@[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!

Replies
8
Views
411
Replies
6
Views
175
Replies
4
Views
166
Replies
5
Views
125
Replies
2
Views
185

1,195,654
Messages
6,010,935
Members
441,575
Latest member
JOHNNY18031

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back