How do I use a variable as a cell reference?

kpm30519

New Member
Joined
Nov 9, 2018
Messages
19
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.

Thanks in advance.
 

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
Joined
Aug 18, 2015
Messages
10,807
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
Joined
Nov 9, 2018
Messages
19
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
Joined
Aug 18, 2015
Messages
10,807
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
Joined
Nov 9, 2018
Messages
19
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top