count(R1C1:R[excelname]C1 worksheet not vba

As the title suggests, I want to use an excel name to define the integer value for the R argument.

So far i have failed in getting the example function (count) to work with using names in the range designation.

I suspect that i need to somehow designate that the datatype of the excel name is an integer, but i do not know how to do that in excel at the worksheet level. I did go to the cell for whose content the name points to
and designated the value as a number with zero decimal places. but I suspect that excel keeps that number in floating point data type.

I am seeking to do this because I want to avoid having to select the range end cell. I want to precalculate that range end cell once and then where ever i need to perform a function use that range end cell.

It is tricky, in my case, as the range end cell is calculated by: DATEDIF(TEXT(R7C1, "dd-mmm-yyyy"), TODAY(), "d")...... This approach allows data to be missing within the data set including the most recent entry. the only requirements are that all my data starts at the first date (R7C1) and that there is a potential to record data for each day.

please help me figure out a way to use variables or constants in an excel worksheet in conjunction with the r1c1 format.

thank you
bil

this is exciting! i did go to the office reference you provided. they are clear but do not use INDIRECT in conjunction with any other function.

What I remain confused about is that I am trying to alter the r1c1 reference in a function. in this case the function is count, but i have other functions as well. as far as I can see, INDIRECT knows nothing about functions. Am i supposed to do something like:

count(INDIRECT("R1C1:R" &NumPotentialMeas& "C1", False) ??????

Upon reflection on your answer I am impressed. It is very logical and I knew enough of the facts you used to have come up with it myself....but I was not that smart.

thank you so much for sharing your experience. I will try your solution immediately.

bil

i will try it immediately.

the above formulat, = COUNT(INDIRECT("R9C8:R["&-1*NumPotentialMeas&"]C8",FALSE)) does work.

But what I did not appreciate is that I can not drag the above across various columns and have the column number change to reflect the column the formula is in. Hence I tried to modify the above formula to the following:

= COUNT(INDIRECT("R9C" &8&":" &"R["&-1*NumPotentialMeas&"]C"&8,FALSE))

Thus R9C is fixed but I pulled out teh column number, 8, hoping the column number might increment. it did not do so. Is that because excel is not able to interpret the number 8 is a column number? how might I do that?

the above formulat, = COUNT(INDIRECT("R9C8:R["&-1*NumPotentialMeas&"]C8",FALSE)) does work.

But what I did not appreciate is that I can not drag the above across various columns and have the column number change to reflect the column the formula is in. Hence I tried to modify the above formula to the following:

= COUNT(INDIRECT("R9C" &8&":" &"R["&-1*NumPotentialMeas&"]C"&8,FALSE))

Thus R9C is fixed but I pulled out teh column number, 8, hoping the column number might increment. it did not do so. Is that because excel is not able to interpret the number 8 is a column number? how might I do that?