Creating a master spreadsheet for sorting information

Notsonerdy · #1 05-30-2016, 02:15 PM

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.

jeffreybrown · #2 05-30-2016, 04:39 PM

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?

Notsonerdy · #3 05-30-2016, 04:54 PM

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...

jeffreybrown · #4 05-30-2016, 05:01 PM

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.

Notsonerdy · #5 05-30-2016, 05:05 PM

That is exactly what I want.

jeffreybrown · #6 05-30-2016, 05:09 PM

Great to hear. Best of luck on your project.

Notsonerdy · #7 05-30-2016, 10:06 PM

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)

Pecoflyer · #8 05-31-2016, 01:01 AM

I moved this thread to the programming forum as it seems to go in that direction

jeffreybrown · #9 05-31-2016, 04:39 AM

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

Also, the reason for the example I choose column I is because A if the drop down is in A then the row would transfer before all the information is filled in.

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