Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to and from Named Formulas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to and from Named Formulas
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) wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to and from Named Formulas
Bob Phillips wrote: 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 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(V29V30,-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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting to and from Named Formulas
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 wrote in message ups.com... Bob Phillips wrote: 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 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(V29V30,-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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting formulas to code | New Users to Excel | |||
Converting formulas to values | Excel Discussion (Misc queries) | |||
Converting formulas to value | New Users to Excel | |||
Using Named Formulas | Excel Worksheet Functions | |||
Want Help Converting Excel Links to Named Cells (i.e. 1 cell Ranges) | Excel Programming |