How to set up reference variables?

Hi C.J. and welcome to the forum.

Your question smells like you need a Named Range or even better a Dynamic Named Range in your formula somewhere.

Read about them at:
http://www.contextures.com/xlNames01.html or
http://www.exceluser.com/explore/dynname1.htm

If you supply a sample worksheet of what you have and need it is much easier to give a more specific answer.

Hi C.J.

How about a formula that uses the

Please Login or Register  to view this content.

function?
Then when you add data it will always use the date of today to pick up which row.

Another way may be to use the

Please Login or Register  to view this content.

function.

I'm blindly guessing without having a sample to see what you really need. How about creating a sample and attaching it, if you are still stumped.

Hi,

Have you found the function in excel of "=Row()" which will return the row number of the cell it is in?

Look at the Grade Examples on http://www.contextures.com/xlFunctions02.html

The important part is the "True" for the Range_Lookup argument. You don't want exact match. You want the Lookup to move down the list and run into a value larger than what is being looked for. NOTE - you must have your list sorted for this type to work correctly.

Hi cjhund,

I think I have an answer for you. The formula involved the last row and the next to last row of values entered. I'm hoping you don't have any blanks. Then you can use the Count function with the Offset function and get the formula. See the attached with a formula in cell B1 of:

Please Login or Register  to view this content.

that you can pull across for all the other columns.

I hope this does it. If there are blanks in the numbers in any column we will need to rethink the formula above.

You shouldn't need to retype those formulas each day.

Maybe develope a UDF using Marvin's formula to use each day?
Then it is just a matter of typing in the name of the UDF rather than the whole string of nested formulas.

HEY - My formula works if there are no blank cells in a column of numbers (up to 3000 rows).

If this didn't work for him my next attempt was to twiddle with Dynamic Named Ranges to see if I could arrive at the LastRow and build a formula instead of using a UDF. A UDF was going to be my last and final attempt. Now we need to see if he comes back to see THE answer. What if he gave up and never visits back again? Sad!

Marvin ... I hate to push my luck, but since that last formula worked so well, I thought I'd bring up the next batch of formulas I have to update. They are very similar to the ones which I just replaced with your formula. An example would be:

=SUM(B17:C17)

Very simply, adding the last two entries from two separate columns together. And again, this columns get updated with a new row of data daily.

I'm playing with the formula you gave me, seeing if I can modify for this SUM formula, but so far I haven't had any luck. Maybe your formula only can work on a single column of data?

Sincere thanks,
C.J.

Hey - no luck involved. You just need to do a simple example. My formula depends on the Count and Offsett fuctions.

Count will tell how many numbers are in a range. I expect you to not have any blanks or non-numbers in the cells being in the range

The Offset is then used starting from the 3rd row and going down the number of numbers found in the count. The third argument of the offset function will move to the right or left. If you want to pick up the number to the right of the current column then make this number a 1 instead of zero.

See the formula in the attached in cell B3 for the formula of:

Please Login or Register  to view this content.

@MarvinP

Going back a few posts - I know your formula works BUT because OFFSET() is volatile may I suggest:

In B1 dragged across:

Please Login or Register  to view this content.

instead of:

Please Login or Register  to view this content.

EDIT: May I further suggest this for OP's latest request

=SUM(INDEX(B$4:C$3000,COUNT(B$4:B$3000),0))

instead of

=SUM(OFFSET($B3,COUNT(B4:B19),0),OFFSET($B3,COUNT(B4:B19),1))

Hi Cutter,

True and I owe you one. Or the OP owes you one.
How would you do the sum of the last value in column B and the same row in Col C using Index? Give him an answer for that one too.

I think I was on the Offset midset as I was working toward a Dynamic Named Range for him and that was going to be the next step.

Hi Cutter,

I guess this is a little bit of different ways of doing stuff. DO dislikes volitile and directs us to not use Offset if possible.
Watersev likes to use multi line code using the ":"
shg likes i, j, k as variables.
I like Cells(Row, "A") but many others like Range("A:" & Row).

When I first realized Count() was a way to get to the last row, I considered Count of a 2 column range and realized that wouldn't work. I guess this led me to sticking with an Offset instead of Indirect.

Thanks again for a different/better way to do this problem.