Hiding rows based on cell contents from another sheet. 1

[4thorns]

I'm trying to find a way to hide a row on sheet 1 if a corresponding cell on sheet 2 is empty. The requirements are as follows:

1. If Sheet2, A1 is empty then hide Sheet1 Row 1.
2. If not then display the contents of Sheet2 A1. (That's the easy part)
3. Updates to Sheet2 have to be reflected on Sheet1 as they occur.
4. I need at least 50 rows on Sheet1 to contain this function based on their corresponding rows in Sheet2.

None of the research I've done has any reference to doing this over multiple sheets. This is the second time in 6 months I've
tried to tackle this problem and I decided it was time to ask for help! Anyone care to share some insight on this?

Doug

 

[davidbeach]

A VBS macro tied to cells in sheet 2 column A changing value or losing the focus?

 

[IDS]

I think the easiest way to do this is with a user defined array function (see attached file).

Please ask if it isn't clear how to use it.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[keithalewis]

In Sheet2!A1 enter =IF(Sheet1!$A1<>0,Sheet1!A1,0).
Copy and paste to appropriate range in Sheet2.
Tools > Options : View, then uncheck Zero values.

Keith A. Lewis
High performance Excel add-ins

http://xll.codeplex.com

 

[4thorns]

Thanks for the replies. I found a script that I think I could doctor up to work with 2 sheets. Then after rethinking my spreadsheet I changed it in such a way that
doesn't require me to hide the rows. I've had to put this on hold for a few days so I haven't really had time to look at your file Doug. I do appreciate the offering
though. Hopefully within the next few days I will be able to get back at it.

Thanks again,
Doug

 

[IDS]

I hope it's of some use to you in the future Doug. It was only a ten minute job, so we'll waive the fees this time

It does illustrate how a fairly simple bit of VBA code, in the form of a User Defined Function, can provide functionality in a flexible way, that would either be difficult with on-sheet methods (and also probably non-robust), or inflexible using VBA code operating on specified ranges.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/