scifibum
Well-known Member
- Joined
- Jul 22, 2004
- Messages
- 503
I'm thinking there should be a worksheet function (or UDF) that will essentially link to another formula, but evaluate it with adjusted ranges.
For example, let's say I build a formula in Sheet1, cell A1 which I will then want to duplicate in several places. For argument's sake it's:
=SUM(Sheet1!B1:C1)
(In reality it would be something more complicated.)
In other locations in the workbook I'd like to be able to do this:
=formula(Sheet1!A1)
where "formula" represents the function I'm looking for - it would use the formula I've entered in Sheet1!A1, but evaluate it with adjusted ranges.
So if I entered =formula(sheet1!A1) in cell A2, it would return the result of =SUM(Sheet1!B2:C2)
...Kind of a formula-based version, instead of range-based, of the "INDIRECT" function
The reason for doing this is sometimes I build complicated formulas which I then duplicate in various locations in the workbook. If I could link back to one cell where the formula is defined, then I could change it only in one place.
I realize that I could accomplish this by simply creating a UDF then using that UDF where I need it - I could then just change the VBA in the UDF.
But I think the method I described above would be a more useful, accessible way to accomplish the same thing.
I'm wondering if the fact that this would have to be evaluated in VBA would make it impossible - would someone have to basically code the equivalent to all the existing worksheet functions in the UDF in order to accomplish this?
Anyway, thanks for any comments you can all provide.
For example, let's say I build a formula in Sheet1, cell A1 which I will then want to duplicate in several places. For argument's sake it's:
=SUM(Sheet1!B1:C1)
(In reality it would be something more complicated.)
In other locations in the workbook I'd like to be able to do this:
=formula(Sheet1!A1)
where "formula" represents the function I'm looking for - it would use the formula I've entered in Sheet1!A1, but evaluate it with adjusted ranges.
So if I entered =formula(sheet1!A1) in cell A2, it would return the result of =SUM(Sheet1!B2:C2)
...Kind of a formula-based version, instead of range-based, of the "INDIRECT" function
The reason for doing this is sometimes I build complicated formulas which I then duplicate in various locations in the workbook. If I could link back to one cell where the formula is defined, then I could change it only in one place.
I realize that I could accomplish this by simply creating a UDF then using that UDF where I need it - I could then just change the VBA in the UDF.
But I think the method I described above would be a more useful, accessible way to accomplish the same thing.
I'm wondering if the fact that this would have to be evaluated in VBA would make it impossible - would someone have to basically code the equivalent to all the existing worksheet functions in the UDF in order to accomplish this?
Anyway, thanks for any comments you can all provide.
