Excel 2007 : Formula cell references

[kevsvette]

Is there a way to have a formulas cell reference based on what I input in anther cell. I have a lot of formulas with similar cell references so I can update them all by just changing the cell references in one cell or a couple cells.

If I had a formula ... =COUNTIF($C5:$H5,">=1") ... in cell AI4

Could I put in cell A1 the reference $C5:$H5. I have multiple rows with the same $C#:$H#
I'm hoping that if I want to change to say $C#:$J# I can do it easily. This keeps all my forumulas the same from week to week when I have to update.

I'm using Excel 2007

[Paul]

Hi Kevsvette,

Try using

=COUNTIF(INDIRECT(A1),">=1")

You can then change the range in A1 to other ranges and your results will update accordingly.

[kevsvette]

That would work except that I have over 100 rows with the same formula using columns C:H as the reference data. If I use indirect for a cell range c1:h1 it would work for the first row but point to the wrong row for all the others.

I was hoping to not have to use find & replace.

[Paul]

Try posting an example workbook and let us know what cells need to change and why.

[kevsvette]

If you have any questions don't hesitate to ask, I tried to explain in the text box, but I think I failed miserably. LOL

Any help would be appreciated.