If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Name - relative reference doesn't calculate
Build a uDF as I suggested earlier.
Function Sum3Above(rng As Range) Application.Volatile If rng.Cells.Count = 1 Then If rng.Row 1 Then Sum3Above = rng.Offset(-1, 0) If rng.Row 2 Then Sum3Above = Sum3Above + rng.Offset(-2, 0) If rng.Row 3 Then Sum3Above = Sum3Above + rng.Offset(-3, 0) End If End Function And call like H12: =Sum3Above(H12) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#12
|
|||
|
|||
Name - relative reference doesn't calculate
Lori's suggestion works for me.
An alternative using Bob's suggestion only with worksheet functions is to make the formula volatile: =SUM(Name)+TODAY()-TODAY() -- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#13
|
|||
|
|||
Name - relative reference doesn't calculate
A few keystrokes shorter:
=SUM(Name)+NOW()*0 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Lori's suggestion works for me. An alternative using Bob's suggestion only with worksheet functions is to make the formula volatile: =SUM(Name)+TODAY()-TODAY() -- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#14
|
|||
|
|||
Name - relative reference doesn't calculate
But the whole problem with it is he wanted a generic function to sum say 3
cells above, so if you have to include a superset of that range, it ceases to be generic. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Lori's suggestion works for me. An alternative using Bob's suggestion only with worksheet functions is to make the formula volatile: =SUM(Name)+TODAY()-TODAY() -- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#15
|
|||
|
|||
Name - relative reference doesn't calculate
And it fails if in the example if name is defined as !A1:A3 then you put
=SUM(Name) in A2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Lori's suggestion works for me. An alternative using Bob's suggestion only with worksheet functions is to make the formula volatile: =SUM(Name)+TODAY()-TODAY() -- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#16
|
|||
|
|||
Name - relative reference doesn't calculate
I'm assuming that Name is correctly defined and, since the OP knows what
they want, they would know not to use such a formula on row 2. Maybe I'm assuming too much? -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... And it fails if in the example if name is defined as !A1:A3 then you put =SUM(Name) in A2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Lori's suggestion works for me. An alternative using Bob's suggestion only with worksheet functions is to make the formula volatile: =SUM(Name)+TODAY()-TODAY() -- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#17
|
|||
|
|||
Name - relative reference doesn't calculate
Bob,
thanks for your comments, using a UDF is a good solution, but I have to use some ten thousand formulas and this would need a lot of time to calculate the book (AFAIK). Regards Werner |
#18
|
|||
|
|||
Name - relative reference doesn't calculate
Biff,
the trick seems to be to get this dammend formula calculated when the dependent cells are changed. Some post earlier I have mentioned that I have found out that the calculation behaviour seems to depend on whether Excel recognizes dependents or not. Example 1: (Excel doesn't calculate automatically, only by pressing "Ctrl+Alt+F9:") Formula in cell "F1" is "=SumEveryThirdValue" and "SumEveryThirdValue" is defined as a named formula "=SUM(!A1,!C1,!E1)". When you check the dependecies with the detective you get no traces on the sheet. Example 2: (Excel calculates automatically) Formula in cell "F1" is "=SUM(RangeSumEveryThirdValue)" and "RangeSumEveryThirdValue" is defined as a named range "=!A1,!C1,!E1". When you check the dependecies with the detective you get traces on the sheet. So the difference between Example 1 and 2 is that in Ex 1 uses a named formula and Ex 2 I uses a named range, which is calculated on the worksheet and has dependent cells (the named range). Question is: is it logic that Example 2 works and what about reliabilty? Regards Werner |
#19
|
|||
|
|||
Name - relative reference doesn't calculate
Werner Rohrmoser wrote...
I use a mane with a relative reference to sum 3 cells to the left or in another case the 3rd, 6th, 9th and 12th cell to the left. The syntax for the name is "!A1:A3" when my pointer is in A4, formula is "=SUM(Name). I wrote it without the sheetname, because I'd like to use it on every sheet I have in the book. Now, when I change values in the precedent cells nothing happens until I force a complete recalculation, it doesn't calculate automatically, also F9 doesn't force the formula to calculate. Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th" case. .... If the purpose of using such names is simplicity and uniformity of formulas in adjacent cells, the better approach would be to use worksheet-level names. For example, in worksheet A with cell A4 active define the name A!name (predecing the name with the worksheet name makes it a worksheet-level name rather than a workbook-level name) referring to A!A1:A3. Enter the formula =SUM(name) in A4 and it returns the sum of the values in A1:A3 in worksheet A. Then copy A!A4 and paste it into B!A4, and that formula will return the sum of the values in A1:A3 in worksheet B *AND* it will have created the worksheet-level name B!name in worksheet B defined in the same way that A!name is defined in worksheet A. The potential disadvantage is that if you want to redefine such names, you must redefine each one on every worksheet. You could use a macro in a different workbook to iterate through all sheets in your main workbook making the same changes to the relevant worksheet-level names. |
#20
|
|||
|
|||
Name - relative reference doesn't calculate
Harlan,
I don't know whether you've red posting 17 in this thread, where I have described my experience with named relative ranges and named formulas with relative ranges. I'm not sure whether Example 2 works in every case. Anyway, I have the feeling based on the discussion in this thread that it's better to avoid named ranges like "=!A1,!A3,!A5" because Excel seems to have a problem with this design (do you have the same experience?) Using worksheet level names to get reliable recalculation along with the benefits of simplicity and uniformity should be my choice. Regards Werner |
Thread Tools | |
Display Modes | |
|
|