Hiding Rows if the linked rows are blank

[KG]

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?

[Max]

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 <at>yahoo<dot>com
----
"KG" <[email protected]> wrote in message
news:[email protected]...
> 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?

[KG]

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 <at>yahoo<dot>com
> ----
> "KG" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Max]

> ... 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 <at>yahoo<dot>com
----

[KG]

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 <at>yahoo<dot>com
> ----
>
>
>

[KG]

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 <at>yahoo<dot>com
> ----
>
>
>

[Max]

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 <at>yahoo<dot>com.
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 <at>yahoo<dot>com
----

[Max]

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 <at>yahoo<dot>com
----

[KG]

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 <at>yahoo<dot>com
> ----
>
>
>

[Max]

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