Converting to and from Named Formulas

[calummurdo]

Hi,

I have a spreadsheet that has lots of Named Formulas (to ensure
consistancy and avoid errors). Each formula refers to data in the three
rows directly above it (i.e. in the same column). The formula is
repeated further down in each column referring to more data.

I've been asked to insert a new row into each set of data, however when
I do this the named formulas don't automatically adjust for it in the
way that a directly inserted formula would i.e. the new row is
referenced when I'd rather point to the original cells. I presume this
is due to the fact that the formulas are relative rather than absolute.


Since the formulas are very complex I don't want to manually adjust
them in the Named Range box. The only thing I can think of is to
convert them all to normal formulas, insert the new row, then convert
back to named ranges. Is there way to sort this out?

Best Regards,

CalumMurdo Kennedy

 

[Bob Phillips]

There are probably easier ways. not easy but easier, but it would help to
see an example formula or two so that we could make a suggestion.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[calummurdo]

There are probably easier ways. not easy but easier, but it would help to
see an example formula or two so that we could make a suggestion.

Hi Bob,

These are the key formulas that I use, there are a number of variations
(12 variations in total) on these but whatever works for these I could
apply to another:
=IF(OR(V37={"AL";"SL";"CL";"ML";"AdL";"PL";"PaL";"OL"}),$Q$41,IF(V37="TL",$Q$41,""))
=IF(OR(V29="",V30="")=TRUE,"",IF(V29>V30,-SUM(V29-SUM(V30+1)),SUM(V30-V29)))
=IF(WEEKDAY(V25)=1,IF(SUM($B26:V26,AK21)=0,"",SUM($B30:V30,AK20)),"")

I've removed the sheet names that was in all the references just so
that it's marginally easier to read. These formulas would normally be
repeated three times in a column pointing to data that is in the same
relative position. Any help on how to change them would be most
appreciated, or an easier way to update the via the named range
dialoge!

Best Regards,

CalumMurdo Kennedy

 

[Peter T]

Unless I'm missing something none of your formulas appear to refer to cells
in multiple rows such as sum(A1:A3). In your OP you said the problem was
catering for rows inserted in such a range.

why, for eg, SUM(V29-SUM(V30+1)) ?

Perhaps your examples don't illustrate the problem so maybe you could do
something like this

Select rows 10:12 and name the range "rowsA"

Select A13 and name following formula say "mySum"
=SUM(INDEX(rowsA,,COLUMN()))

=mySum in any cell outside 'rowsA' and it should return the sum of three
cells in 'rowsA' in whatever column you entered the formula. Now if you
insert or delete rows in 'rowsA' or move 'rowsA' your formula should update.

As you say it can be difficult editing long refersto formulas in the Names
wizard. Probably easier to copy paste or define the names programmatically.

I note your named formulas include a lot of relative addressing which in
turn is relative to the active cell at the time the formulas were named.
Similarly if programmatically naming your formulas ensure the correct cell
is selected.

Alternatively get hold of the Namemanager addin from the authors' sites
of Jan Karel Pieterse and Charles Williams (download sections).
www.jkp-ads.com
www.DecisionModels.com

- much easier to edit names with this

Regards,
Peter T