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
|
|||
|
|||
Summing non hidden values in a range
Afraid I can't, I don't have 2003.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... its not working. i m sending you sample workbook. please check what is the problem. +-------------------------------------------------------------------+ |Filename: Book1.zip | |Download: http://www.excelforum.com/attachment.php?postid=4714 | +-------------------------------------------------------------------+ -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=537953 |
#12
|
|||
|
|||
Summing non hidden values in a range
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 |
#13
|
|||
|
|||
Summing non hidden values in a range
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 |
#14
|
|||
|
|||
Summing non hidden values in a range
Problem with a UDF is that it is not recalculated if a new row gets
hidden/unhidden. You would have to force it somehow. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "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 |
#15
|
|||
|
|||
Summing non hidden values in a range
=subtotal(109,....) works nicely with hidden rows--not so nicely with hidden
columns in xl2003. Bob Phillips wrote: Something like =SUBTOTAL(109,A1:E1) should work in those circumstances. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format Column Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns. Bob Phillips Wrote: How have you hidden the rows? -- HTH Bob Phillips -- 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 |
#16
|
|||
|
|||
Summing non hidden values in a range
Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with
columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave Peterson" wrote in message ... =subtotal(109,....) works nicely with hidden rows--not so nicely with hidden columns in xl2003. Bob Phillips wrote: Something like =SUBTOTAL(109,A1:E1) should work in those circumstances. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format Column Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns. Bob Phillips Wrote: How have you hidden the rows? -- HTH Bob Phillips -- 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 |
#17
|
|||
|
|||
Summing non hidden values in a range
=subtotal(9,a1:e1)
didn't exclude cells in hidden columns for me in xl2003. (Did you really mean to type columns in your question?) Bob Phillips wrote: Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave Peterson" wrote in message ... =subtotal(109,....) works nicely with hidden rows--not so nicely with hidden columns in xl2003. Bob Phillips wrote: Something like =SUBTOTAL(109,A1:E1) should work in those circumstances. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format Column Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns. Bob Phillips Wrote: How have you hidden the rows? -- HTH Bob Phillips -- 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 -- Dave Peterson |
#18
|
|||
|
|||
Summing non hidden values in a range
I am not sure what I was thinking now, as it couldn't have worked. You can't
filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor implementation though, they obviously just carried the existing functionality, without considering that whilst columns may not get hidden by filter, they can by manually hiding. Bob "Dave Peterson" wrote in message ... =subtotal(9,a1:e1) didn't exclude cells in hidden columns for me in xl2003. (Did you really mean to type columns in your question?) Bob Phillips wrote: Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave Peterson" wrote in message ... =subtotal(109,....) works nicely with hidden rows--not so nicely with hidden columns in xl2003. Bob Phillips wrote: Something like =SUBTOTAL(109,A1:E1) should work in those circumstances. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format Column Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns. Bob Phillips Wrote: How have you hidden the rows? -- HTH Bob Phillips -- 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 -- Dave Peterson |
#19
|
|||
|
|||
Summing non hidden values in a range
It sure seems like a small step (to me, anyway) to make =subtotal(1##,...) work
with hidden columns. But who the heck knows, well outside of MS? Bob Phillips wrote: I am not sure what I was thinking now, as it couldn't have worked. You can't filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor implementation though, they obviously just carried the existing functionality, without considering that whilst columns may not get hidden by filter, they can by manually hiding. Bob "Dave Peterson" wrote in message ... =subtotal(9,a1:e1) didn't exclude cells in hidden columns for me in xl2003. (Did you really mean to type columns in your question?) Bob Phillips wrote: Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave Peterson" wrote in message ... =subtotal(109,....) works nicely with hidden rows--not so nicely with hidden columns in xl2003. Bob Phillips wrote: Something like =SUBTOTAL(109,A1:E1) should work in those circumstances. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format Column Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns. Bob Phillips Wrote: How have you hidden the rows? -- HTH Bob Phillips -- 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 -- Dave Peterson -- Dave Peterson |
#20
|
|||
|
|||
Summing non hidden values in a range
That'll be a new feature in Excel 14.
Biff "Dave Peterson" wrote in message ... It sure seems like a small step (to me, anyway) to make =subtotal(1##,...) work with hidden columns. But who the heck knows, well outside of MS? Bob Phillips wrote: I am not sure what I was thinking now, as it couldn't have worked. You can't filter a row, so if anything in A1:E1 is filtered, it all is. Oh well. Poor implementation though, they obviously just carried the existing functionality, without considering that whilst columns may not get hidden by filter, they can by manually hiding. Bob "Dave Peterson" wrote in message ... =subtotal(9,a1:e1) didn't exclude cells in hidden columns for me in xl2003. (Did you really mean to type columns in your question?) Bob Phillips wrote: Thanks Dave, that explains it. Do you know why as SUBTOTAL(9 works fine with columns? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave Peterson" wrote in message ... =subtotal(109,....) works nicely with hidden rows--not so nicely with hidden columns in xl2003. Bob Phillips wrote: Something like =SUBTOTAL(109,A1:E1) should work in those circumstances. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "starguy" wrote in message ... I have hidden columns not rows. I hide them by both ways using Ctrl+9 and by menu Format Column Hide but nothing happened after hiding. I also pressed F9 to recalculate after hiding columns. Bob Phillips Wrote: How have you hidden the rows? -- HTH Bob Phillips -- 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 -- Dave Peterson -- 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 |