Solved

populating an inventory style excel spreadsheet with another excel form

Posted on 2011-03-16
13
414 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

778 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