Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hiding rows based on cell contents from another sheet. 1

Status
Not open for further replies.
4thorns

4thorns

Structural
Joined
Jan 22, 2009
Messages
152
Location
US
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
 
Sort by date Sort by votes
A VBS macro tied to cells in sheet 2 column A changing value or losing the focus?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Histor
  • Locked
  • Question Question
Autogenerate Typical Rows based on input value
Replies
4
Views
429
SkipVought
SkipVought
dik
  • Locked
  • Question Question
Convert Cell Types
Replies
10
Views
1K
dik
dik
dgallup
  • Locked
  • Question Question
Ancient spread sheet formats
Replies
13
Views
675
oldrunner
oldrunner
dik
  • Locked
  • Question Question
Copying Cells
Replies
6
Views
981
dik
dik
electricpete
  • Locked
  • Question Question
an update to vba routine for highlighting the current cell
Replies
6
Views
480
skeletron
skeletron

Part and Inventory Search

Sponsor

Back
Top