If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Would like help with displaying data if are a certain value is pre
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. |
#2
|
|||
|
|||
Would like help with displaying data if are a certain value is pre
OK, formatting there didn't work
Please look at http://homepage.ntlworld.com/a.coles/Excel.jpg Hope this makes it a little clearer |
#4
|
|||
|
|||
Would like help with displaying data if are a certain value is pre
That isn't too clear either. Otto
"Adrian" wrote in message ... OK, formatting there didn't work Please look at http://homepage.ntlworld.com/a.coles/Excel.jpg Hope this makes it a little clearer |
#5
|
|||
|
|||
Would like help with displaying data if are a certain value is
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) |
#6
|
|||
|
|||
Would like help with displaying data if are a certain value is
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... "Adrian" wrote: 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) |
#7
|
|||
|
|||
Would like help with displaying data if are a certain value is
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 "Adrian" wrote in message ... 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) |
#8
|
|||
|
|||
Would like help with displaying data if are a certain value is
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 "Adrian" wrote in message ... 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... "Adrian" wrote: 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) |
#9
|
|||
|
|||
Would like help with displaying data if are a certain value is pre
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Inserting a new line when external data changes | Rental Man | General Discussion | 0 | January 11th, 2006 07:05 PM |
Cross program data sharing | m_jurrens | General Discussion | 1 | December 16th, 2005 05:29 PM |
Add data labels without displaying data points? | Brenda | Charts and Charting | 3 | October 27th, 2005 04:10 AM |
How do I save an access document in word document? | cmartin | General Discussion | 2 | September 13th, 2005 11:26 PM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |