F
Francis Hookam
There are many occasions when I want to use a variable in a spreadsheet
formula for instance:
R1C1 might have =COUNT(R9C:R100C) giving the result 62 since, on this
occasion only, R9C1:R70C1 are occupied
The formula in say R1C5 is =R1C1-COUNTA(R9C:R100C)
I should like to replace the 100 of R100C with the variable, that is the
contents of R1C1, something like this:
=R1C1-COUNTA(R9C:R & R1C1 & C)
or
=R1C1-COUNTA(R9C:R + R1C1 + C)
or even
=R1C1-COUNTA(R9C:R&TEXT(R1C1, "0")&C)
Of course I could extend R9C:R100C to R9C:R1000C or whatever to the bottom
of the sheet, to cover any eventuality, but it would be much neater to have
the flexibility of using a variable held in another cell
How can I do this please
Francis Hookham
formula for instance:
R1C1 might have =COUNT(R9C:R100C) giving the result 62 since, on this
occasion only, R9C1:R70C1 are occupied
The formula in say R1C5 is =R1C1-COUNTA(R9C:R100C)
I should like to replace the 100 of R100C with the variable, that is the
contents of R1C1, something like this:
=R1C1-COUNTA(R9C:R & R1C1 & C)
or
=R1C1-COUNTA(R9C:R + R1C1 + C)
or even
=R1C1-COUNTA(R9C:R&TEXT(R1C1, "0")&C)
Of course I could extend R9C:R100C to R9C:R1000C or whatever to the bottom
of the sheet, to cover any eventuality, but it would be much neater to have
the flexibility of using a variable held in another cell
How can I do this please
Francis Hookham