Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

populating an inventory style excel spreadsheet with another excel form

Posted on 2011-03-16
13
415 Views
Last Modified: 2012-05-11
I would like to populate an inventory spreadsheet with the contents of another excel worksheet.  Idea being that each time the worksheet is filled out, say after hitting an enter or print button, it would create a new row of corresponding data to the inventory spreadsheet.

If anyone has a simple/no code solution.  it would be greatly appreciated.

0
Comment
Question by:barelysane
  • 8
  • 5
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35153753
>>>>dea being that each time the worksheet is filled out, say after hitting an enter or print button, it would create a new row of corresponding data to the inventory spreadsheet.

barelysane: The above shouldn't be a problem. May I see a sample file so that I get my references right?

Sid
0
 

Author Comment

by:barelysane
ID: 35154019
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154081
Quick question. For example for "Task 1" can both values in the check box remain empty or one needs to be ticked when updating the other sheet?

Sid
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:barelysane
ID: 35154087
it can be empty
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154089
Ok. I am almost done.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154092
Wait. One last question. :) What if both are ticked for task 1? Which value should I pickup?

Sid
0
 

Author Comment

by:barelysane
ID: 35154103
it should only be yes,no, or neither, not both.  thanks
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154105
Ok. Gimme 10 mins

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 35154159
Sample file attached. Do remember to change the output files name and path in the code below. I have documented the code so that it is easier to understand.

Sid

Code Used

Dim i As Long, LastRow As Long
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim fFile As String

Private Sub CommandButton1_Click()
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    
    '~~> Check if Y/N is not ticked together
    If ValidateCB("Check Box 2", "Check Box 3") = False Or _
    ValidateCB("Check Box 12", "Check Box 13") = False Or _
    ValidateCB("Check Box 14", "Check Box 15") = False Or _
    ValidateCB("Check Box 16", "Check Box 17") = False Or _
    ValidateCB("Check Box 18", "Check Box 19") = False Or _
    ValidateCB("Check Box 20", "Check Box 21") = False Or _
    ValidateCB("Check Box 22", "Check Box 23") = False Or _
    ValidateCB("Check Box 24", "Check Box 25") = False Or _
    ValidateCB("Check Box 26", "Check Box 27") = False Then
        MsgBox "Please select only Y or N"
        Exit Sub
    End If
    
    '~~> Check if location is not empty
    If Len(Trim(ws1.Range("B3").Value)) = 0 Then
        MsgBox "Location cannot be empty"
        Exit Sub
    End If
    
    '~~> Changes the path here
    fFile = "C:\Sample-Worksheet-to-populate.xlsx"
    
    '~~> Open the workbook to populate
    Set wb2 = Workbooks.Open(fFile)
    Set ws2 = wb2.Sheets("Sheet1")
    
    '~~> get the lastrow to be populated
    LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    '~~> Store location
    ws2.Range("A" & LastRow).Value = ws1.Range("B3").Value
    
    '~~> Get the Y/N values
    If ws1.CheckBoxes("Check Box 2").Value = 1 Then
        ws2.Range("B" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 3").Value = 1 Then
        ws2.Range("B" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 12").Value = 1 Then
        ws2.Range("C" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 13").Value = 1 Then
        ws2.Range("C" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 14").Value = 1 Then
        ws2.Range("D" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 15").Value = 1 Then
        ws2.Range("D" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 16").Value = 1 Then
        ws2.Range("E" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 17").Value = 1 Then
        ws2.Range("E" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 18").Value = 1 Then
        ws2.Range("F" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 19").Value = 1 Then
        ws2.Range("F" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 20").Value = 1 Then
        ws2.Range("G" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 21").Value = 1 Then
        ws2.Range("G" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 22").Value = 1 Then
        ws2.Range("H" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 23").Value = 1 Then
        ws2.Range("H" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 24").Value = 1 Then
        ws2.Range("I" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 25").Value = 1 Then
        ws2.Range("I" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 26").Value = 1 Then
        ws2.Range("J" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 27").Value = 1 Then
        ws2.Range("J" & LastRow).Value = "N"
    End If
    
    '~~> Save and close the workbook
    wb2.Close savechanges:=True
    
    '~~> inform user that data is save successfully
    MsgBox "Data Saved"
    
    '~~> Clear data for next input
    ws1.Range("B3").ClearContents
    For i = 12 To 27
        ws1.CheckBoxes("Check Box " & i).Value = -4146
    Next
    ws1.CheckBoxes("Check Box 2").Value = -4146
    ws1.CheckBoxes("Check Box 3").Value = -4146
End Sub

Function ValidateCB(CB1 As String, CB2 As String) As Boolean
    ValidateCB = True
    If ws1.CheckBoxes(CB1).Value = 1 And _
    ws1.CheckBoxes(CB2).Value = 1 Then
        ValidateCB = False
    End If
End Function

Open in new window

Sample-Form.xlsm
0
 

Author Comment

by:barelysane
ID: 35158561
Sid,

Looks very promising.  Issue with the output though, see attached.  

Thanks!
Sample-Worksheet-to-populate.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35158674
Did you change anything in the code because it is working absolutely fine here.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35158700
Output Sample attached

Sid
Sample-Worksheet-to-populate.xlsx
0
 

Author Comment

by:barelysane
ID: 35158913
Got it.  thanks for your help!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question