Go Back   Hardware Canucks > SOFTWARE > O/S's, Drivers & General Software

    
Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old April 3, 2012, 10:22 AM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default Question for the Excel guru's out there

I know some of you have helped me in the past with excel questions I've had so I'm trying my luck again.

I have a spreadsheet listing a bunch of field production batteries that truck volumes into our facility.
There are around 70 different batteries that truck in, but in any given month there are usually only 10-20 of the 70 that actually deliver.
Since I don't really want to see the info if they didn't truck in I've been manually hiding the rows with a zero value (i.e. nothing trucked in)

Is there some way, via macro's or something else, that I can basically say: "If value = zero, then hide row x"

It's further complicated that there are two columns containing values for each battery, and sometimes one column has a zero but the other has a value, in which case I wouldn't want to hide that row.

So I guess what I'm really looking for is "If the values in columns A AND B for row x = zero, then hide row x.

So? Is this possible to do? Not a big deal if it isn't as I've just been hiding the rows manually for the last 6 years, but quicker ways to do things are always good :)
Reply With Quote
  #2 (permalink)  
Old April 3, 2012, 09:05 PM
grinder's Avatar
Allstar
F@H
 
Join Date: Mar 2007
Posts: 822

My System Specs

Default

you can't toggle row's/columns's to be visible or not.

At best I would:

a) set your XLS file into two sheets. 1 as a back-end for raw data (batteries 1 thru 70), the other sheet that reports on said data using macros and what-not. (you can use Visual Basic inside this "dashboard" XL sheet to query the back-end raw data sheet and make it display much more cleanly).

b) dump excel and move to MS-Access. It's better at entering data, storing data, compiling data and reporting on data.
__________________
Phenom II 945 :: ASUS M4A78-E (780G) :: BFG 285GTX :: 4GB Mushkin DDR2 (5-4-4-12) :: Creative Xi-Fi :: Seagate 500 gig 7200.12 (better than WD BLACK!!!!!) :: Samsung 2493HM
Reply With Quote
  #3 (permalink)  
Old April 3, 2012, 09:24 PM
PerryC's Avatar
Allstar
F@H
 
Join Date: Apr 2011
Location: Burton, NB
Posts: 737

My System Specs

Default

Conditional formatting is what you are looking for. I am not sure you can use it to actually hide rows, but I do know you can use it to return zeros, or blank any cell that is empty.

Which version of excel are you using? I can give you more precise details once I know that.
__________________
The oath to serve your country did not include a contract for normal luxury, and comforts enjoyed within our society. On the contrary it implies hardships, loyalty and devotion to duty, regardless of rank
Reply With Quote
  #4 (permalink)  
Old April 3, 2012, 09:46 PM
Hall Of Fame
 
Join Date: Oct 2007
Posts: 1,582
Default

you could also sort the rows to have the zeroes on the bottom to "hide" them.
Reply With Quote
  #5 (permalink)  
Old April 4, 2012, 08:15 AM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default

Using Excel 2010.
I'd consider using access but we don't have it here.

I've used conditional formatting elsewhere but there's no option to hide rows that I could see.
Sorting by value might be an idea...would certainly make it easier to hide them all together rather than line-by-line.

I haven't used macros much so was wondering if there would be a way to do it by using them.
Reply With Quote
  #6 (permalink)  
Old April 4, 2012, 09:08 AM
"Quote This..."
F@H
 
Join Date: Nov 2007
Location: Hell
Posts: 3,833
Default

Set the row height to 0 if cell A + cell B = 0. Else set row height to 12.
Reply With Quote
  #7 (permalink)  
Old April 5, 2012, 08:44 AM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default

Quote:
Originally Posted by Squeetard View Post
Set the row height to 0 if cell A + cell B = 0. Else set row height to 12.
That'd work, but how exactly do I go about doing that?
Reply With Quote
  #8 (permalink)  
Old April 5, 2012, 08:15 PM
grinder's Avatar
Allstar
F@H
 
Join Date: Mar 2007
Posts: 822

My System Specs

Default

good thinking squeetard, the row height works nice! Sample spreadsheet attached. GregH... you will need to change your excel options to display the developer ribbon so you can see the VB code.

http://www.caffeinatedinc.net/shared...ightmacro.xlsm

This should point you in the right direction
__________________
Phenom II 945 :: ASUS M4A78-E (780G) :: BFG 285GTX :: 4GB Mushkin DDR2 (5-4-4-12) :: Creative Xi-Fi :: Seagate 500 gig 7200.12 (better than WD BLACK!!!!!) :: Samsung 2493HM
Reply With Quote
  #9 (permalink)  
Old April 6, 2012, 05:06 PM
Allstar
 
Join Date: Aug 2007
Location: Rocky Mountain House
Posts: 837

My System Specs

Default

Quote:
Originally Posted by grinder View Post
good thinking squeetard, the row height works nice! Sample spreadsheet attached. GregH... you will need to change your excel options to display the developer ribbon so you can see the VB code.

http://www.caffeinatedinc.net/shared...ightmacro.xlsm

This should point you in the right direction
That nearly does what I need, but I'd still need to see the values in column C.

Looks very promising so far though - thanks a bunch!
Reply With Quote
Reply


Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help With Excel AmuseMe Off Topic 2 November 23, 2011 10:04 AM
Question for the Excel guru's out there GregH O/S's, Drivers & General Software 12 October 27, 2010 02:46 PM
EXCEL help LC0g O/S's, Drivers & General Software 15 February 3, 2010 11:55 AM
question for wc guru's gingerbee Water Cooling 13 March 31, 2009 08:52 PM
question for you rosetta guru's Phobia HardwareCanucks F@H Team 12 February 21, 2009 08:30 AM