Would like help with displaying data if are a certain value is pre

[Adrian]

Hi,
Hope the formatting of this comes out OK.
Typed it up in notepad 1st.

Anyone good at functions where if 1 cell in a row = a value, then the entire
row is displayed onto another sheet
If one of a few row below it also has a cell in the same column with that
value, then the two rows are added up and only one row is displayed in the
initial one. However, if this happens and one of those rows that has this
value has text in an area, then the text is placed below the displayed line.

For example, if on sheet1 I have the following on Sheet1:

Price Length Width Height Weight Locks Hinges Handles Wheels Dividers Extra Info Selected?
Product1Spec1 10 30 20 10 10 1 2 1 4 1
Product1Spec2 20 60 30 20 20 2 2 2 4 2 1
Product1Spec3 30 90 40 30 30 2 3 2 6 3
Product1Upgrade1 5 1
Product1Upgrade2 5 1 1
Product1Upgrade3 10 1 1
Product1Upgrade4 10 1
Product1Upgrade5 15 Brass Locks
Product1Upgrade6 15 Brass Handles
Product1Upgrade7 20 Brass Hinges No Extras 1

And on Sheet2 I have:

Product2Spec1 10 30 20 10 10 1 2 1 4 1
Product2Spec2 20 60 30 20 20 2 2 2 4 2
Product2Spec3 30 90 40 30 30 2 3 2 6 3 1
Product2Upgrade1 5 1 1
Product2Upgrade2 5 1 1
Product2Upgrade3 10 1 1
Product2Upgrade4 10 1
Product2Upgrade5 15 Brass Locks 1
Product2Upgrade6 15 Brass Handles 1
Product2Upgrade7 20 Brass Hinges 1


On Sheet3 I would like to have automatically displayed:


Product1Spec2 35 60 30 20 20 2 3 3 4 2


Product2Spec3 70 90 40 30 30 3 4 3 6 3
Brass Locks
Brass Handles
Brass Hinges

Eventually this will have loads of these blocks of items and upgrades etc,
with the final page being a kind of order summary page without listing what
is not being ordered.

Hope this makes sense

Any help really appreciated.

 

[Adrian]

OK, formatting there didn't work

Please look at http://homepage.ntlworld.com/a.coles/Excel.jpg

Hope this makes it a little clearer

 

[Otto Moehrbach]

Adrian
What you want to happen cannot be accomplished with functions. A
function can only return a value and only in the cell that holds the
function. A function cannot move things around. Functions can pull data
from somewhere to the cell that holds the function, but you want more that
that. You will need VBA (automation).
The explanation you gave in your post is entirely too much to follow,
particularly by the way your data is rearranged in these messages. I can
take the time to help you with this if you wish. Send me your file or an
example of your file. If you feel your data is proprietary just make up
some data. I need just the data layout and the names of the sheets
Include as much detail explanation as you can about what you have, with
examples, lots of examples, and about what you want to happen (this moves
there, etc). Examples of before and after are very useful. Include the
version of Excel you are using.
My email address is (e-mail address removed). Remove the "nop" from this
address, HTH Otto

 

[Otto Moehrbach]

That isn't too clear either. Otto

 

[Adrian]

OK, I can understand that what I want to do is very complicated.

Before I email you direct, I have thought of a simpler, if not so go solution.

Could someone show how to create a button or something that when clicked
hides any rows that do not have a value in a certain column?

(that would work just as well as I could set the page up not to print the
column that is used to say something is selected).

If I don't get an answer on this one, then I'll email you direct Otto.
(Thanks for the offer)

 

[Adrian]

Just realised that with this way, I would also need to know how to make a
total excluding hidden rows...

I.E. - total cell is =sum(a1:a25) but excluding the values in the hidden
rows...

 

[Otto Moehrbach]

Adrian
This macro will hide all rows that have a blank cell in Column E. Note
that this macro assumes that Column A has a non-blank entry in the last row
of your data so that Excel will know what the last row of your data is in
order to hide the last row of your data if that row has a blank cell in
Column E.
Copy this macro to a standard module.
Then select the sheet that has your data.
Click on View - Toolbars - Forms.
The second icon from the top in the right column is a button icon.
Click on that icon.
Go to where you want the button to be.
Hold down the left button of your mouse and draw the button on the sheet.
Release the mouse button.
A dialog box pops up asking you to assign a macro to that button. Select
this macro and click OK.
That's it.
HTH Otto
Sub HideRows()
Dim RngColA As Range
Dim i As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If IsEmpty(i.Offset(, 4)) Then i.EntireRow.Hidden = True
Next i
End Sub

 

[Otto Moehrbach]

Summing only the visible rows will take a UDF (User Defined Function). Put
the following function (written by Dave Peterson) in a standard module (you
can use the same module as before if you want).
Then, in the cell where you want the sum, type "=SumVisible(the range you
want summed)"
For instance:
=SumVisible(A2:A10).
The UDF is:
Function SumVisible(rng As Range)
Application.Volatile
Dim myTotal As Double
Dim myCell As Range
myTotal = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If myCell.EntireRow.Hidden = False _
And myCell.EntireColumn.Hidden = False Then
myTotal = myTotal + myCell.Value
End If
End If
Next myCell
SumVisible = myTotal
End Function

 

[Country]

how can gridlines be retained when sending mail from Excel. If sent by
attachment the file will have the lines but if sent as in the email
text--lines are eliminated.