Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
KG
 
Posts: n/a
Default Hiding Rows if the linked rows are blank

Here is the problem:

I have one worksheet (we'll call it "Segment Data") containing more rows
than are normally necessary, to accomodate projects that happen to have more
segments that are average. For most projects, many of the rows will not be
needed and will, consequently, be left blank.

A second worksheet (we'll call it "Sales Summary") will be designed with as
many rows as "Segment Data" and will be auto-populated with data from the
corresponding rows in "Segment Data." Therefore, if any rows in "Segment
Data" are unused and are left blank, the corresponding rows in "Sales
Summary" will be displaying all zeros.

Is it possible to use Conditional Formatting or other techniques to hide the
rows in "Sales Summary" if their corresponding rows in "Segment Data" are
blank?
  #2   Report Post  
Max
 
Posts: n/a
Default

Try this set-up ..

Assume you have

In Segment Data
--------------------
In cols A to C, the table:

Project# Field1 Field2
1111 Data1 Data11

1112 Data2 Data12

1113 Data3 Data13

etc

where blank rows (could be 1 blank row, could be several blank rows) have
been set aside for each project to accomodate insertion of future data. It's
assumed that col A is the key column, which will be filled with the project
# from above (1111, 1112, etc) should data input be made in the blank row(s)

Using an empty col to the right, say col E?
Put in E2: =IF(A2="","",ROW())
Copy E2 down to say, E100 to cover the max expected data range

In Sales Summary
---------------------
With the same col headers in A1:C1 :
Project# Field1 Field2

Put in A2:

=IF(ISERROR(SMALL('Segment Data'!$E:$E,ROWS($A$1:A1))),"",INDEX('Segment
Data'!A:A,MATCH(SMALL('Segment Data'!$E:$E,ROWS($A$1:A1)),'Segment
Data'!$E:$E,0)))

Copy A2 across to C2, fill down to C100
(cover the same range as in col E in Sheet1)

Sales Summary will auto-display only those rows from Segment Data which are
populated in col A (Project#). And these will be bunched at the top, with
"blank" rows thrown below - which should hence achieve the same visual
effect spelled out in your lines:

... to hide the rows in "Sales Summary"
if their corresponding rows in "Segment Data" are blank?


Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"KG" wrote in message
...
Here is the problem:

I have one worksheet (we'll call it "Segment Data") containing more rows
than are normally necessary, to accomodate projects that happen to have

more
segments that are average. For most projects, many of the rows will not be
needed and will, consequently, be left blank.

A second worksheet (we'll call it "Sales Summary") will be designed with

as
many rows as "Segment Data" and will be auto-populated with data from the
corresponding rows in "Segment Data." Therefore, if any rows in "Segment
Data" are unused and are left blank, the corresponding rows in "Sales
Summary" will be displaying all zeros.

Is it possible to use Conditional Formatting or other techniques to hide

the
rows in "Sales Summary" if their corresponding rows in "Segment Data" are
blank?



  #3   Report Post  
KG
 
Posts: n/a
Default

I'll give this a try except I'm not entirely clear about "1111 Data1, Data11,
1112 Data 2, Data 12". Are they meant to represent sample data?

To clarify:

My "Segment Data" worksheet will have 60 rows (many of which will be unused
in most cases) and 11 columns. Column A will contain the segment description.
Columns B:K will display the segment sales (number format).

Then, "Sales Summary" will re-display the sales, except that the unused rows
must be hidden. Also, "Sales Summary" will need to have a "TOTAL" row which
will use the SUM function to total up each column and which should appear
right below the last populated row.




"Max" wrote:

Try this set-up ..

Assume you have

In Segment Data
--------------------
In cols A to C, the table:

Project# Field1 Field2
1111 Data1 Data11

1112 Data2 Data12

1113 Data3 Data13

etc

where blank rows (could be 1 blank row, could be several blank rows) have
been set aside for each project to accomodate insertion of future data. It's
assumed that col A is the key column, which will be filled with the project
# from above (1111, 1112, etc) should data input be made in the blank row(s)

Using an empty col to the right, say col E?
Put in E2: =IF(A2="","",ROW())
Copy E2 down to say, E100 to cover the max expected data range

In Sales Summary
---------------------
With the same col headers in A1:C1 :
Project# Field1 Field2

Put in A2:

=IF(ISERROR(SMALL('Segment Data'!$E:$E,ROWS($A$1:A1))),"",INDEX('Segment
Data'!A:A,MATCH(SMALL('Segment Data'!$E:$E,ROWS($A$1:A1)),'Segment
Data'!$E:$E,0)))

Copy A2 across to C2, fill down to C100
(cover the same range as in col E in Sheet1)

Sales Summary will auto-display only those rows from Segment Data which are
populated in col A (Project#). And these will be bunched at the top, with
"blank" rows thrown below - which should hence achieve the same visual
effect spelled out in your lines:

... to hide the rows in "Sales Summary"
if their corresponding rows in "Segment Data" are blank?


Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"KG" wrote in message
...
Here is the problem:

I have one worksheet (we'll call it "Segment Data") containing more rows
than are normally necessary, to accomodate projects that happen to have

more
segments that are average. For most projects, many of the rows will not be
needed and will, consequently, be left blank.

A second worksheet (we'll call it "Sales Summary") will be designed with

as
many rows as "Segment Data" and will be auto-populated with data from the
corresponding rows in "Segment Data." Therefore, if any rows in "Segment
Data" are unused and are left blank, the corresponding rows in "Sales
Summary" will be displaying all zeros.

Is it possible to use Conditional Formatting or other techniques to hide

the
rows in "Sales Summary" if their corresponding rows in "Segment Data" are
blank?




  #4   Report Post  
Max
 
Posts: n/a
Default

... Are they meant to represent sample data?
Yes

Have a go at trying it out. Think it's still viable.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #5   Report Post  
KG
 
Posts: n/a
Default

I will give it a try and will report back :-)

"Max" wrote:

... Are they meant to represent sample data?

Yes

Have a go at trying it out. Think it's still viable.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----





  #6   Report Post  
KG
 
Posts: n/a
Default

Max, one last question before I give it a try:

Will the linked rows is segment data need to be contiguous? I am thinking of
providing three rows with sales scenarios (base, upside, and downside), the
fourth row being the row with the selected scenario. The data from the fourth
row would then feed to the Sales Summary. In Sales Summary the rows will be
contiguous

"Max" wrote:

... Are they meant to represent sample data?

Yes

Have a go at trying it out. Think it's still viable.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----



  #7   Report Post  
Max
 
Posts: n/a
Default

For the simple criteria formula suggested in col E, the cautious answer is
probably a "yes". But while you tinker with the example set-up, perhaps you
could also email over a copy of your file ?
Send to: demechanik <atyahoo<dotcom.
I'll take a look. It's getting kinda hard to figure out what's happening <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #8   Report Post  
Max
 
Posts: n/a
Default

If you've sent over the file, I haven't received
Let me know ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #9   Report Post  
KG
 
Posts: n/a
Default

I didn't send the file because I had to change the "Segment Data" design to
include a scenario arrangement whereby an "Active Scenario" row will appear
at 8-row intervals. Nested (IF) formulas determine which of the scenario rows
to drop into the "Active Scenario" row. Consequently the Active Scenario row
will be populated with zeros even if the data block was not used at all -- no
more blank rows.

I will use a different technique to determine if the data block should be
treated as unused, but I have not yet figured out how to hide those unused
rows in "Sales Summary" . I think I will need VBA code to accomplish that.

Thanks again for your help and interest

"Max" wrote:

If you've sent over the file, I haven't received
Let me know ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----



  #10   Report Post  
Max
 
Posts: n/a
Default

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete blank rows John Mansfield Excel Discussion (Misc queries) 3 April 27th 05 11:48 PM
Hiding blank rows mlkpied Excel Discussion (Misc queries) 1 March 29th 05 08:57 PM
Blank Rows Acesmith Excel Discussion (Misc queries) 1 November 30th 04 10:23 PM
Inserting Blank Rows Macro? Michael Saffer Excel Worksheet Functions 2 November 9th 04 07:23 PM
Copying Rows when hiding other rows Neutron1871 Excel Worksheet Functions 2 November 4th 04 12:38 AM


All times are GMT +1. The time now is 07:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"