Using Cell Variables to Specify Cell Range

[jcottam]

Hi There,

I'm looking to make a worksheet as dynamic as possible.

I want to define a range of ROWS (rows 15 - 30) in table cells (ex: A1=
15 , A2= 30)

I then want to reference these definitions in a formula like the one
below:

=SUM(COUNTIF(DataWorkSheet!L$*15*:L$*30*,$A16)/$B$10)

Unfortunately I don't know how to reference variables "15" and "30" to
make the formula above dynamic. My dream is to input row numbers
(range) into cells A1 and A2 and then reference the "VALUES" of these
cells in the formula above. Basically, I don't want to hard code "15"
and "30" into the formula above.

Any suggestions? Thanks!

 

[Guest]

you can use cells(n,"L") n is variable.

 

[Ken Wright]

Please don't multipost - You have a number of answers across different
groups now, and that really is frowned upon in these groups.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Guest]

=SUM(COUNTIF(INDIRECT("DataWorkSheet!L$"&E4&":L$"&E5),$A16)/$B$10)

In your Worksheet you would set
where e4=15 and e5=30
You can also make any other part of the string a variable (i.e. DataWorkSheet)
Hope this is what you are looking for.
Felix