# How do I use a variable as a cell reference?

#### kpm30519

##### New Member
I have 8000+ rows, with a few being added each day. I need to run calculations on the last ten, 20, or other number rows throughout the day, but with rows being added every day, I have no fixed point from which to start. The last entry could be in row D2520 this morning, but D2564 this afternoon.

I can get the value from the last filled cell (LOOKUP(2,1/(D:D<>""),D:D)) and the row number of the last filled cell (LOOKUP(2,1/(D:H<>""),ROW(D:H))), but I don't know how to, for example, SUM(DLastRowNumber:DLastRowNumber-10). I was tinking a variable equal to the number of rows I need but I don't know how to implement it in a cell reference.

If there is another solution, I'm all for it. I'd rather a formula, but a macro or function is fine. FYI, I'm a VBA newbie.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Eric W

##### MrExcel MVP
I haven't seen the video, but here's one option, if the last row number (your second formula) is in F6:

=SUM(INDEX(D:D,F6-9):INDEX(D:D,F6))

#### kpm30519

##### New Member
So, the above works, but I have an array, so I tried COUNTIF((INDEX(D:H,(U59-9)):INDEX(D:H,U59)),S51), but I get a #REF error.

Ideas?

#### Eric W

##### MrExcel MVP
Are you trying to do a COUNTIF from D91:H100 (if your last row is 100)? Then try:

=COUNTIF((INDEX(D:D,(U59-9)):INDEX(H:H,U59)),S51)

First cell (identified with the INDEX) is the upper left, next cell is bottom right.

#### kpm30519

##### New Member
OMFG...

I've been banging my head for hours on this and just didn't see it.

If anyone is interested, my final formula is this: =COUNTIF((INDEX(\$D:\$D,(LOOKUP(2,1/(\$D:\$H<>""),ROW(\$D:\$H)))):INDEX(\$H:\$H,(LOOKUP(2,1/(\$D:\$H<>""),ROW(\$D:\$H)-\$R53)))),S\$51)

This will take the last X non- empty rows of data (X is stored for now in R53) dynamically from array (D:H), and count the number of "hits" (S51). It is slow, but it work. If you see something wrong, or if you can suggest a better way, I'd really appreciate any thoughts.

Replies
4
Views
155
Replies
0
Views
214
Replies
4
Views
407
Replies
3
Views
91
Replies
4
Views
240

1,129,590
Messages
5,637,279
Members
416,963
Latest member
samfuge

### 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