#1
|
|||
|
|||
Creating a master spreadsheet for sorting information
Hi there,
So I am creating a workbook, for work, for our marketing, and I need help. We are completely revamping what has been done before and I was told to start new. What I'd like to do: Have a raw data page where all the information for every client we get can go. Drop down menues on the first column which will help separate the data into different sheets for easier mailmerge purposes. a formula, that will take the information entered in a row, and send it to the correct sheet when the specific drop down choice has been made. For example: Raw data page Code | Address | Name | 1 | 122 Avenue | Bob | <-- This data with code 1 will then be sent to page 1. 2 | 156 street | Joe | <-- This data with code 2 will then be sent to page 2. and so on... This way, rather than having 5 or 10 separate workbooks I can then have all the information entered on 1 raw data sheet and then separated into the specific sheets for and if I have to search for a specific name I don't have to spend hours going through previous workbooks to find it. If this is possible please let me know. **Side note, I have already found how to make the drop down menues, I just don't know where to go from here. |
#2
|
|||
|
|||
Hello & Welcome to the Forum,
My suggestion would be to use a Worksheet change event on the master sheet which will send the information automatically to the individual sheets. Do you want to use macros? |
#3
|
|||
|
|||
If I knew what any of what you just typed meant I'm sure it would mean something. Lets just say that my computer expertise is very minimal, I can write letters, do basic excel spread sheets and check email. LOL. Which is why I have come to the forums for help.
I honestly don't care if I understand how it works, I would just like to know how to make it work... |
#4
|
|||
|
|||
Look at this example and see if it is something you can work with.
On the Sheet1 tab, use the dropdown box in column I to select one of the choices. Of the three choices, each choice has its own sheet. When you select one of the three choice the row will be copied to the sheet that matches the choice. |
#5
|
|||
|
|||
That is exactly what I want.
|
#6
|
|||
|
|||
Great to hear. Best of luck on your project.
|
#7
|
|||
|
|||
Ok, So I've had a chance to play around with the worksheet you sent, and it is pretty awesome.
I showed it to my husband who has more scripting knowledge than me (not hard really) And he showed me how to change the script to make it so that Column A is the one where I can put the drop down menues and then showed me how to make the new drop down menues, but we seem to be having an issue when we change the specific item. It doesn't delete from the sheet the info was sent to. I have tried re downloading the workbook but I seem to be having the same issue, even if I don't change anything, the information just stays on the sheet after I've changed the information on the raw data page. Is there something we can put into the code page to get the information deleted from the sheet if the drop down menue changes? Is it something I've done when I changed the script from Column I to Column A? ( I know I said I didn't need to understand earlier but apparently my interest has been peaked) |
#8
|
||||
|
||||
I moved this thread to the programming forum as it seems to go in that direction
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
I don't have much time this morning, but a couple of things.
If you look at the code, I left a line in there to delete the row which was just moved. Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim sname As String Dim rng As Range Set rng = Target.Parent.Range("I:I") If Target.Count > 1 Then Exit Sub If Intersect(Target, rng) Is Nothing Then Exit Sub sname = Range("I" & Target.Row).Value Target.EntireRow.Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) Target.EntireRow.Delete End Sub Seems going from left-to-right would be the better way and then transfer info when you get to the end. I feel I might be missing something so please fill in the gaps where I don't understand. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to open Multiple files and copy information to a master file | gbaker | Excel Programming | 2 | 04-08-2016 08:44 AM |
One formula required to copy information from YTD spreadsheet onto individual monthly spreadsheets. | Jo Freeman | Excel | 4 | 10-28-2015 08:09 AM |
Creating a list and sorting alphabetically. | irvsax | Word | 4 | 09-04-2013 11:47 PM |
Creating a sorting method. | Balliol | Word | 2 | 07-25-2013 06:18 AM |
CAUTION!! Sorting a spreadsheet with hidden columns will trash your data. | psmaster@earthlink.net | Excel | 0 | 11-24-2009 11:54 AM |