Author Topic: Excel help  (Read 3176 times)

0 Members and 1 Guest are viewing this topic.

Online Slateman

  • Posts: 41405
Excel help
« Topic Start: January 29, 2015, 01:06:31 PM »
Not even sure if what I want to do is possible, nor worth the effort. But it never hurts to ask.

 I have a rather long list of supplies. I would like to list them on different worksheets in the same workbook. IE, paper supplies, name badges, binding supplies, etc. I would then like to have all of the supplies, item numbers, amounts, and any notes in the note columns put on a separate worksheet as the master list.

 I've seen this once, but I can't remember where the spreadsheet is and cant' find anyone who remembers who originated it. But it had a button on the main sheet that said click to generate and it took all the data from the other worksheets and compiled it in to one master sheet.

 The columns on all the worksheets will have the same titles (Item, Number available, Notes, Location).

Can anyone explain to me how to do this?

Offline Dave B

  • Posts: 6036
Re: Excel help
« Reply #1: January 29, 2015, 03:40:55 PM »
My first inclination is to put everything in the master sheet and give each line a column with the category (paper, badges, etc) then have the separate sheets populate off the master sheet by searching/referencing that category column. Vlookup might be the function to use.

Or you can just use one sheet and use filters. Select "paper" to show all the paper. You can select any number of categories

Offline imref

  • Posts: 24148
  • 1B: The New Hot Corner
Re: Excel help
« Reply #2: January 29, 2015, 04:10:04 PM »
maybe overkill, but check out pivot tables:

http://www.timeatlas.com/excel-pivot-tables/


Offline Nathan

  • Global Moderator
  • ****
  • Posts: 10724
  • Wow. Such warnings. Very baseball. Moderator Doge.
Re: Excel help
« Reply #3: January 29, 2015, 04:15:06 PM »
My first inclination is to put everything in the master sheet and give each line a column with the category (paper, badges, etc) then have the separate sheets populate off the master sheet by searching/referencing that category column. Vlookup might be the function to use.

Or you can just use one sheet and use filters. Select "paper" to show all the paper. You can select any number of categories

I think this would be a better solution.

Offline tomterp

  • Global Moderator
  • ****
  • Posts: 31923
  • Hell yes!
Re: Excel help
« Reply #4: January 29, 2015, 08:53:25 PM »
Always build the data in a master database if possible, per Dave's advice.

Per IMRef's advice, use pivot tables to analyze.  You won't even need multiple sheets, but if you insist anyway  :roll:  just select your sheet data using the pivot table, and copy to another sheet, then rinse and repeat for each of your selections.

And yes, filters are another way to display only the data you want to see in the master database.   But pivot tables are more flexible and powerful, can summarize data by item counts, $, etc.

If you don't know how to use pivot tables, you should learn how because they're easy to use and really useful.

Online comish4lif

  • Posts: 2836
  • Too Stressed to care.
Re: Excel help
« Reply #5: January 30, 2015, 07:01:27 AM »
Slate, is this for work or home?

I agree with having everything on one sheet, a master database. The easiest way to.separate the data would be with filters. That way everything is in one place and there's no need for formulas.

Using vlookup or index/match to have a subset for paper allows the opportunity to get out of sync if you update your master but not the subset.

Online Slateman

  • Posts: 41405
Re: Excel help
« Reply #6: January 30, 2015, 07:27:25 AM »
It's for work. I've seen it done before. It's easier to break supplies down by category and then have it all combine to a master roster than the other way around.

Offline HalfSmokes

  • Posts: 18636
Re: Excel help
« Reply #7: January 30, 2015, 09:00:13 AM »
You should try pivot tables, knowing how to create and work with them is a pretty valuable skill

Online Slateman

  • Posts: 41405
Re: Excel help
« Reply #8: January 30, 2015, 09:32:15 AM »
I'm pretty sure a pivot table isn't want I want

Four worksheets: A, B, C, D
Each worksheet has the same four columns: Item, Number, Location, and Notes

On Workshop E, the master list, I want the same four columns (Item, Number, Location, and Notes), but all of the data under those columns from each worksheet. I may, or may not, want them listed alphabetically (Item).

Offline Nathan

  • Global Moderator
  • ****
  • Posts: 10724
  • Wow. Such warnings. Very baseball. Moderator Doge.
Re: Excel help
« Reply #9: January 30, 2015, 10:09:30 AM »
I'm pretty sure a pivot table isn't want I want

Four worksheets: A, B, C, D
Each worksheet has the same four columns: Item, Number, Location, and Notes

On Workshop E, the master list, I want the same four columns (Item, Number, Location, and Notes), but all of the data under those columns from each worksheet. I may, or may not, want them listed alphabetically (Item).

Code: [Select]
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Delete the summary sheet if it exists.
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Master").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ' Add a new summary worksheet.
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Master"

    ' Fill in the start row.
    StartRow = 2

    ' Loop through all worksheets and copy the data to the
    ' summary worksheet.
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

            ' Find the last row with data on the summary
            ' and source worksheets.
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            ' If source worksheet is not empty and if the last
            ' row >= StartRow, copy the range.
            If shLast > 0 And shLast >= StartRow Then
                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

               ' Test to see whether there are enough rows in the summary
               ' worksheet to copy all the data.
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                   MsgBox "There are not enough rows in the " & _
                   "summary worksheet to place the data."
                   GoTo ExitTheSub
                End If

                ' This statement copies values and formats.
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With

            End If

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)
   
    ' Copy column headers and freeze top row
    Range("A1").EntireRow.Insert
    Sheets("Paper Supplies").Rows(1).Copy Sheets("Master").Rows(1)
   
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
     
    ' AutoFit the column width in the summary sheet.
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
   
End Sub

Online Slateman

  • Posts: 41405
Re: Excel help
« Reply #10: January 30, 2015, 10:45:34 AM »
So I got this:

Quote
Public Sub UpdateMaster()

    Dim wrkBk As Workbook
    Dim wrkSht As Worksheet
    Dim wrkShtMaster As Worksheet
    Dim rngDataLastCell As Range
    Dim lMasterLastRow As Long
   
    Set wrkBk = ThisWorkbook
    Set wrkShtMaster = wrkBk.Worksheets("Master")
   
    wrkShtMaster.Cells.ClearContents
    For Each wrkSht In ThisWorkbook.Worksheets
        If wrkSht.Name <> "Master" Then
            Set rngDataLastCell = LastCell(wrkSht)
            lMasterLastRow = LastCell(wrkShtMaster).Row + 1
            With wrkSht
                'Copy the headers to row 1.
                .Range(.Cells(1, 1), .Cells(1, rngDataLastCell.Column)).Copy _
                    Destination:=wrkBk.Worksheets("Master").Cells(1, 1)
               
                'Copy the data to the bottom of the Master Sheet.
                .Range(.Cells(2, 1), rngDataLastCell).Copy _
                    Destination:=wrkShtMaster.Cells(lMasterLastRow, 1)
               
            End With
        End If
    Next wrkSht
   
End Sub


Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range


    Dim lLastCol As Long, lLastRow As Long
   
    On Error Resume Next
   
    With wrkSht
        If Col = 0 Then
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        Else
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
        End If
       
        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1
       
        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0
   
End Function


Public Sub CreateRightClickButton(sCaption As String, sOnAction As String)

    Dim cBut As CommandBarButton
   
    On Error Resume Next
    'The 'Cell' commandbar is the right-click menu that pops up.
    'First we need to delete any controls that have the caption we are going to use.
    'We then add a new blank temporary control.
    With Application
        .CommandBars("Cell").Controls(sCaption).Delete
        Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
    End With
   
    'Once the new control has been added we choose the style of button, the caption and which
    'macro fires when the button is pressed.
    With cBut
        .Caption = sCaption
        .Style = msoButtonCaption
        .OnAction = sOnAction
    End With
    On Error GoTo 0

End Sub
End Sub



Public Sub DeleteRightClickButton(sCaption As String)


    'Delete any controls in the 'Cell' commandbar that have the named caption.
    On Error Resume Next
            With Application
                .CommandBars("Cell").Controls(sCaption).Delete
            End With
    On Error GoTo 0


End Sub
End Sub

Private Sub CommandButton21_Click()

End Sub

I created a button that will run the macro. But how do I get it to sort things by the Item, alphabetically? Or by location number? Or, for a different project, by time?

Offline Dave B

  • Posts: 6036
Re: Excel help
« Reply #11: January 30, 2015, 11:00:03 AM »
Wow. Some needledick got paid. Prolly shoulda put that crap in Access

Offline Nathan

  • Global Moderator
  • ****
  • Posts: 10724
  • Wow. Such warnings. Very baseball. Moderator Doge.
Re: Excel help
« Reply #12: January 30, 2015, 12:00:05 PM »
So I got this:

I created a button that will run the macro. But how do I get it to sort things by the Item, alphabetically? Or by location number? Or, for a different project, by time?

Fine don't use mine.  Also, use the sort button.

Offline MarquisDeSade

  • Posts: 14870
  • I am Djour Djilios.
Re: Excel help
« Reply #13: January 30, 2015, 12:50:16 PM »
Fine don't use mine.  Also, use the sort button.

:clap:

Sanity.

Online Slateman

  • Posts: 41405
Re: Excel help
« Reply #14: January 30, 2015, 12:55:35 PM »
Fine don't use mine.  Also, use the sort button.
Sorry man, I didn't see yours until after I had come up with mine. Thanks though.
Wow. Some needledick got paid. Prolly shoulda put that crap in Access
Next step, as Access will allow me to put these in a different format, but I already have that part built out, for the most part. But doing it in a spreadsheet will help with planning events and injects. It will allow events to be split off in to threads and written/planned chronologically, without dealing with other events/injects that other people are planning.

Offline Dave B

  • Posts: 6036
Re: Excel help
« Reply #15: January 30, 2015, 01:05:45 PM »
I was joking about access, but I don't know the details of your stuff. I would have done yours in all one sheet and used filters and been done about 15 seconds

Online Slateman

  • Posts: 41405
Re: Excel help
« Reply #16: January 30, 2015, 01:15:14 PM »
I was joking about access, but I don't know the details of your stuff. I would have done yours in all one sheet and used filters and been done about 15 seconds
Access has it's uses. It will input appropriate information in to the appropriate form, but it makes developing products more difficult as there's no real timeline that you can edit when you want.

I could have easily done filters, but that wouldn't have taught me anything I didn't know already.

Offline tomterp

  • Global Moderator
  • ****
  • Posts: 31923
  • Hell yes!
Re: Excel help
« Reply #17: February 03, 2015, 09:38:57 PM »
I'm pretty sure a pivot table isn't want I want

Four worksheets: A, B, C, D
Each worksheet has the same four columns: Item, Number, Location, and Notes

On Workshop E, the master list, I want the same four columns (Item, Number, Location, and Notes), but all of the data under those columns from each worksheet. I may, or may not, want them listed alphabetically (Item).

Sure it is.  Though, you'd probably need a 5th column - to be able to parse the data out as you've used whole sheets for above - A, B, C, and D.  So, in each row of the fifth column would be whatever names your sheets have.

But Filters would also work if your data is in a single sheet.

Online Vega

  • Posts: 5230
  • Supremely Fashionable
Re: Excel help
« Reply #18: February 08, 2015, 10:27:15 PM »
How do I add up all of the numbers in a column in an Access query?

Online Vega

  • Posts: 5230
  • Supremely Fashionable
Re: Excel help
« Reply #19: February 08, 2015, 10:41:15 PM »
Never mind. I just inserted a total into the query results.

Online Vega

  • Posts: 5230
  • Supremely Fashionable
Re: Excel help
« Reply #20: March 04, 2015, 07:10:35 PM »
I need to input data validation criteria with a min of 15 and a max of 65, but it also must equal less than 75% of the value in the cell to its left. How do I do that?

Offline Natsinpwc

  • Posts: 9695
Re: Excel help
« Reply #21: March 04, 2015, 07:16:22 PM »
Whatever happened to Lotus 1-2-3?  I was pretty good with that.

Offline Nathan

  • Global Moderator
  • ****
  • Posts: 10724
  • Wow. Such warnings. Very baseball. Moderator Doge.
Re: Excel help
« Reply #22: March 04, 2015, 07:25:41 PM »
I need to input data validation criteria with a min of 15 and a max of 65, but it also must equal less than 75% of the value in the cell to its left. How do I do that?

Custom validation using AND.

Offline tomterp

  • Global Moderator
  • ****
  • Posts: 31923
  • Hell yes!
Re: Excel help
« Reply #23: March 04, 2015, 08:39:53 PM »
Custom validation using AND.

Sure, that works.  Could do it with "IF" statements, but AND is more direct.

=AND(J7>15,J7<65,J7<(0.75*K7))

Where the number you're testing is in J7, and the number in the cell immediately to the left is in K7.

Offline Nathan

  • Global Moderator
  • ****
  • Posts: 10724
  • Wow. Such warnings. Very baseball. Moderator Doge.
Re: Excel help
« Reply #24: March 04, 2015, 09:02:28 PM »
For the first two, you'd want to do >= and <= or modify the values since you don't want to exclude the min value or max value.

I'm curious, how would you set it up using IF?  Nested IF statements?

=IF(J7>=15, IF(J7<=15, IF(J7<(0.75*K7), TRUE, FALSE), FALSE), FALSE) ?