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 |
#21
|
|||
|
|||
Summing non hidden values in a range
You're skipping excel 12 and excel 13????
O, ye of little faith. Biff wrote: That'll be a new feature in Excel 14. Biff snipped |
#22
|
|||
|
|||
Summing non hidden values in a range
You're skipping excel 12 and excel 13????
12's already "in the box". I think they may be superstitious and skip 13 and go right to 14. Biff "Dave Peterson" wrote in message ... You're skipping excel 12 and excel 13???? O, ye of little faith. Biff wrote: That'll be a new feature in Excel 14. Biff snipped |
#23
|
|||
|
|||
Summing non hidden values in a range
what should I do now...problem persists. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#24
|
|||
|
|||
Summing non hidden values in a range
Ardus, I tried your UDF but I get a result of 0 all the time (with or
without hidden columns) Biff "Ardus Petus" wrote in message ... I don't have XL 2003 either.. Sounds like you need an UDF. Here is some code you can paste in a Module '------ Function TOTAL_VISIBLE(rng As Range) As Long Dim c As Range For Each c In rng With c If Not .EntireColumn.Hidden Then TOTAL_VISIBLE = TOTAL_VISIBLE + .Value End If End With Next c End Function '--------- "starguy" a écrit dans le message de news: ... what should I do. I need it... any body esle... -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#26
|
|||
|
|||
Summing non hidden values in a range
Try JB's UDF.
But note that hidding/unhidding columns/rows does not trigger a calculation so the formula will not update until a calculation is either automatically triggered or you manually calculate by hitting function key F9. Biff "starguy" wrote in message ... what should I do now...problem persists. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#27
|
|||
|
|||
Summing non hidden values in a range
Did you enter a valid range? (eg: =total_visible(A1:A4) )
Yes. I got it to return a sum but it doesn't change when columns are hidden and I calculate. I had changed the function name but didn't realize it was called later in the procedure. I changed it back. Biff "Ardus Petus" wrote in message ... Works fine by me. Did you enter a valid range? (eg: =total_visible(A1:A4) ) -- AP "Biff" a écrit dans le message de news: ... Ardus, I tried your UDF but I get a result of 0 all the time (with or without hidden columns) Biff "Ardus Petus" wrote in message ... I don't have XL 2003 either.. Sounds like you need an UDF. Here is some code you can paste in a Module '------ Function TOTAL_VISIBLE(rng As Range) As Long Dim c As Range For Each c In rng With c If Not .EntireColumn.Hidden Then TOTAL_VISIBLE = TOTAL_VISIBLE + .Value End If End With Next c End Function '--------- "starguy" a écrit dans le message de news: ... what should I do. I need it... any body esle... -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#28
|
|||
|
|||
Summing non hidden values in a range
Here's a workaround that will work in any version of Excel: In row 1 Column D enter =CELL("width",A2) Copy to cells E1 through K1 in Cell L11 enter =SUMIF(D1:K1,"0",D11:K11) -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#29
|
|||
|
|||
Summing non hidden values in a range
Can you explain that, it doesn't work for me?
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "CaptainQuattro" CaptainQuattro.277voy_1146635100.8876@excelforu m-nospam.com wrote in message news:CaptainQuattro.277voy_1146635100.8876@excelfo rum-nospam.com... Here's a workaround that will work in any version of Excel: In row 1 Column D enter =CELL("width",A2) Copy to cells E1 through K1 in Cell L11 enter =SUMIF(D1:K1,"0",D11:K11) -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#30
|
|||
|
|||
Summing non hidden values in a range
Try adding:
Application.volatile to the top of the procedu Function TOTAL_VISIBLE(rng As Range) As Long application.volatile ..... This tells excel to recalculate the function whenever excel recalculates. If I recall correctly, some versions of excel won't recalc when columns are hidden/unhidden--maybe all versions??? (Changing the columnwidth--not hiding/showing--causes a recalc in xl2003, though.) So for UDFs like these, you'll want to force a recalc before you trust the results. Biff wrote: Did you enter a valid range? (eg: =total_visible(A1:A4) ) Yes. I got it to return a sum but it doesn't change when columns are hidden and I calculate. I had changed the function name but didn't realize it was called later in the procedure. I changed it back. Biff "Ardus Petus" wrote in message ... Works fine by me. Did you enter a valid range? (eg: =total_visible(A1:A4) ) -- AP "Biff" a écrit dans le message de news: ... Ardus, I tried your UDF but I get a result of 0 all the time (with or without hidden columns) Biff "Ardus Petus" wrote in message ... I don't have XL 2003 either.. Sounds like you need an UDF. Here is some code you can paste in a Module '------ Function TOTAL_VISIBLE(rng As Range) As Long Dim c As Range For Each c In rng With c If Not .EntireColumn.Hidden Then TOTAL_VISIBLE = TOTAL_VISIBLE + .Value End If End With Next c End Function '--------- "starguy" a écrit dans le message de news: ... what should I do. I need it... any body esle... -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=537953 -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula: If 2 values (in a range of six) are >3 then TRUE, FALSE | IFfunction | General Discussion | 2 | October 10th, 2005 10:34 AM |
Summing values within a range | rmellison | General Discussion | 7 | September 2nd, 2005 12:43 PM |
Cell linked to a range of cell values in different sheet | szeng | General Discussion | 1 | August 9th, 2005 02:41 AM |
Want to put range values in a column into two columns | Arch | Worksheet Functions | 7 | June 15th, 2004 11:49 PM |
Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP | Bob Lidgard | Charts and Charting | 14 | February 25th, 2004 03:26 PM |