When to strip absolute dollar signs?

[ChrisGreaves]

I am still fossicking around range names (please see "Range Name definition wonky (Excel 2000)") and this morning am looking at replacing a defined range name with its cell reference.

Code: Select all

TranslateFormulaFromRangeNameWKS(ActiveSheet, "Actual_Suction_Pressure_Known_Override", "Abacus!$B$24")]

I program to search for any cell (formula) that contains the string "Actual_Suction_Pressure_Known_Override" and plan to substitute "Abacus!$B$24".
For my purposes, If I find myself in the worksheet "Abacus" then I plan on substituting only "$B$24", as my process of eliminating all use of the range name continues.

But now it strikes me that in some/many/most cases I don't necessarily want or need an absolute reference; I'd be better served by substituting just "B24".
By default, Excel2000 seems to assign an absolute cell address to each defined range name.
Excel adjusts the cell reference (assigned to the range name) when I insert or delete cells away from the referenced cell.

It seems to me that when I start using range names, I must lose all sense of whether the original cell reference was relative or absolute, and therefore cannot map correctly back to the user's original intention. I must elect to go with fully-absolute cell references or fully-relative cell-references, and hope for the best.

[HansV]

If you create a name by selecting a range and typing a name in the Name box on the left hand side of the formula bar, the definition of the new name will use an absolute address, e.g. =Abacus!$B$24.

But if you use Insert | Name | Define... to create a name, you can decide whether the definition of the name uses absolute, relative or mixed cell references.
For example, if the active cell is D5, and you define a name referring to =Abacus!E5, that name will refer to the cell to the right of the active cell. So if the active cell is B37, the name refers to C37, etc.
And if the active cell is D5, and you define a name referring to =Abacus!D$1, that name will refer to the cell in row 1 in the same column as the active cell. So if the active cell is B37, the name will refer to B1, etc.

But if a formula uses named ranges, you have no way to know the original intention of the user creating the formula - if any.

[ChrisGreaves]

But if a formula uses named ranges, you have no way to know the original intention of the user creating the formula - if any.

Thanks Hans, this confirms my belief, that information is lost when the end-user creates a range name.
I think that any utility code that converts range names (in formulas) back to cell references (in formulas) will have a user option that strips or sustains absolute addressing.
I might retain absolute addressing for rectangular arrays of cells ...

[rory]

Can't you check the name's RefersToRange property?

[HansV]

Of course, and that tells you whether the name definition uses absolute, relative or mixed cell addressing. But if you have a formula that uses a defined name, you don't know what kind of addressing it used before it used the defined name - it doesn't necessarily have to be the same as in the defined name.

[rory]

Ah, I guess I misunderstood the question then. I thought this was just about removing names from formulas - I would assume that if the formula is working, then using the current definition would be fine.

[ChrisGreaves]

Ah, I guess I misunderstood the question then.

Thank you Rory, so gracious. More likely a "poorly-phrased question" methinks . Thanks Hans too for stepping up to the plate.

Rory you are correct in assuming that the workbook works; I am looking at rehabilitation of correctly functioning workbooks, subs-section "range names". I have code to create, list, delete range names etc.
Now I'm looking at a situation where range names have been created, but with problems (perhaps confusion between local/global definitions, or based on typographically incorrect text labels), and the requirement is to strip all range names from the workbook (or from a worksheet, or from a selection) and then re-create the range names automatically.

This last part is a piece of cake from my point of view; both defining the range names and replacing cell references with the newly-defined range names.

Right now I am struggling with the first part: I have found a range name, determined a formula where that range name is used, and want to convert, say
"=POCKETIN * 2"
to
"=$E$26 * 2" or "=E26 * 2".
Should I go absolute or relative?
Posts in this thread suggest that the decision cannot be made automatically, as it would be dependent on the original range-name-creator's intention.

I'm no longer hung up on this; I'm going to go ahead with generic code that will test a run-time switch and
(1) Make Absolute
(2) Make Relative
(3) Leave as is
although I think that the defined range names will always arrive as absolute, so option (3) is redundant.

[rory]

Personally I would probably replace the range with its current definition (whether that be relative, absolute or a mixture) and make a log sheet of the changes made for reference later if the sheet doesn't work anymore!