Automatically Hide/Unhide Rows with a 0 Value that contain a formula in Cell A

[mikeb7500]

It seems like I've copied and pasted every example I could find, and none have worked. Sheet One, is the Input Sheet, that populates the values in Sheet Two which is the Invoice Sheet(Sheet Two, all Cells are totally locked).

I have 10 devices(rows) I can enter in the input sheet(sheet 1). The next row would be the totals. If I only enter 7 devices(rows), I would like the remaining 3 rows to be hidden(sheet two), like on an invoice. If I need to add an 8th device(row) it will unhide that 8th row on Sheet Two, when I enter the information in the appropriate Cell in the input sheet, etc.

I'm trying to avoid having empty rows between the device list and the totals.

My first Device Column/Row starts at A8, and my last Device Column/Row ends at A26. An example of the formula in Column A is: =IF('Input Sheet'!$B15>0,'Input Sheet'!$B15,""). So if I don't enter anything in the input sheet Cell $B15, the value is 0 and the A8 row hides. Help

 

[mikeb7500]

When I copy and pasted the code into Sheet 2, (When I do, the two drop down lists say Worksheet and Activate), so all that's in that window is your code, nothing more, nothing less. I didn't change the 123 yet...then I went to File, then I selected Close and Return to Microsoft Excel. I then went to View and then clicked on Macros. When the Macros window appeared, I made sure it said the name of my Workbook, and there is nothing on the list to select. When I pasted it, should I have pasted it in the Activate, or Selectionchange, or should I have selected something else?

 

[mikeb7500]

Jason, I just installed DROPBOX, and put my spreadsheet in it. If you guide me on what to do now, I've never used Dropbox...that way you can take a look at the spreadsheet. Thanks!

 

[jasonb75]

It looks like you've done everything correct with the code, it doesn't show in the list because it's an event, it runs automatically when certain things happen in the sheet, in your case, when you click the sheet2 tab.

If you try before you change the password then the code should give an error warning.

If you open dropbox, then right click your workbook it should give you an option to copy link, which you can paste to the forum.

 

[mikeb7500]

Here is the link to my spreadsheet. https://www.dropbox.com/s/5tl16cpoafaj9lp/PTC Catalog Spreadsheet 073114.xltx

 

[mikeb7500]

Jason, IT WORKED!!!! Thank you so much for all your help and your patience...I did change the password...now I'm going to see if I can get it to work on the other 3 sheets...I'll let you know. Again, thanks SO much!

 

[mikeb7500]

Jason, your code word excellently and I was able to paste it into the other three sheets I needed...thanks again for all your help.

 

[jasonb75]

Thanks for the feedback Mike, glad it helped.

I had a quick look at your sheet and noticed that you have some errors returned by formula, The one I noticed are all caused by this formula in Input sheet B59, there maybe others that I didn't see.

=IF(AND($D$59=1),"$34.95",IF(AND($D$59>=2,$D$59<19),"$24.95",IF(AND($D$59>=19,$D$59<99),"$18.95",IF(AND($D$59>98),"$14.95"," "))))

Your dollar amounts are text strings, not numeric values, so excel can't calculate them, the correct format to make it work would be.

=IF(AND($D$59=1),34.95,IF(AND($D$59>=2,$D$59<19),24.95,IF(AND($D$59>=19,$D$59<99),18.95,IF(AND($D$59>98),14.95," "))))

Then format the cell as currency. Also as a pointer, the last part to return a blank when D59 = 0 or empty should be "" not " ". While not critical, it can make things a lot easier if you're trying to create formula that ignore blank cells.

In your formula, it might be better to return a 0 value rather than a blank, as you have other formula that calculate the result, a blank will still return errors even if the other values are all valid. That said, if a value > 0 is mandatory in D59 then the error would alert the user to the fact that something is incorrect.

I'm wondering why you didn't use the lookup table in the data sheet for this for this formula, =IFERROR(VLOOKUP(D59,'Data Sheet'!$F$2:$G$5,2),"")
For that to work you would need to correct the typo in the data sheet, F3 should be 2, F4 should be 19.

Also check your user guidance instructions in Input sheet B61 and B65, the 'click cell' references don't look right.

Hope this is of some use.

 

[mikeb7500]

Thanks Jason, I will check these out. At the time I did that formula, I didn't know about look up tables. I appreciate all your time.