Excel 2007 : Conditional Formatting: Multiple conditions

**mike_braden** · 03-01-2011, 02:12 PM

All,

I am new to the Forum and thank you all in advance for your help. I am fairly adequate with Excel, but Programming and macros are my ignorance.

I am trying to simplify my life and write a code that when I paste data from a report it will extrapolate out to the corresponding sheets based off of the data it needs. Example is as follows, all US State Based info:

I will paste data into "Master Leads" Worksheet, Any leads from states ID, MT, OR, & WA, the row associated with that state gets copied to Worksheet "Agent 1". Each Agent worksheet getting leads from their respective territory which i can add in myself, I just don't even know where to start.

Again, I appreciate all help and knowledge on the subject, if this is of no challenge to you that is great, I may have some VPN reporting questions in the near future.

Thanks to all!

**JBeaucaire** · 03-01-2011, 06:48 PM

I would add a column to the "master leads" and enter the "territory code" there. That can occur via a formula of some kind, or simply enter the agent. The entries in this column work best if they match the names of the other sheets exactly.

Then, here's a macro for parsing rows of data from one sheet to many sheets named for the same values in a specific column.

Sheet1 to Many Sheets

It not only can parse the rows, it can create the sheets if they are missing. There is a sample sheet there where you can test this out. Just edit the macro so the vCol variable points to the correct column where you put in the agent codes. Also edit the ws variable sheetname from "data" to "Master Leads".

It should work at that point.

**mike_braden** · 03-02-2011, 01:28 PM

Jerry,

Thank you for your prompt reply.

The state codes that will fall into column G: are by formula via the reporting software and are selected by a drop down on the website, so they will be static in compliance with USA State abbreviations, since they are already set, is there still a need for a territory code? Or are you suggesting that I implement a code for "Agent 1" and his states of say ID, OR, MT & WA are coded as "4" and therefore sent to his page based off of the code not the state abbreviation?

The pages are set with the Agents name in my actual workbook, one of which is a catchall for all states not covered by an agent. so there will only be the 7 pages, no more, no less.

I am incredibly green when it comes to entering Macro code and manipulating such codes, would you be so kind as to guide me how to enter the code in excel and which values in your incredible formula I should look at changing??

Please forgive my ignorance.

Thank you!

**JBeaucaire** · 03-02-2011, 04:00 PM

If you're not going to add an agent assignment column, then you'll need to add an Assignment sheet, at least. On this sheet create a two column list. Column A is state abbreviations, Column B is the agent assigned. Include your catchall here.

Do that and post up your modified sheet, I'll see about editing that macro for you to parse all the data out based on the data sheet and the assignment sheet.

**mike_braden** · 03-02-2011, 05:19 PM

Jerry,

Thanks again so much.

Here is the modified workbook with the Assignment sheet at the end.

Thank you for all of your help.

**JBeaucaire** · 03-02-2011, 06:37 PM

Here's the tweaked version of the macro.

Please Login or Register  to view this content.

I installed that into your workbook and hid the ASSIGNMENT sheet.

Remember, you don't actually have to make the agent sheets. Just set the STATE assignments on the assignments sheet and whatever names you put in column B will be used. The macro will create any sheets that it needs to.

**mike_braden** · 03-02-2011, 07:56 PM

Jerry,

Again, thank you so incredibly much. I will give it a go tomorrow morning when I run the report. You truly are a genius at excel. So in reality, I can change the "Agent" fields and the program will run new worksheets to whatever the states are associated with?

In the near future, I may be contacting you, with funding, to write a program that will access a vpn, pull this report, format it, extrapolate it, and email it.

Your help is so greatly appreciated, and your timely response.

I will give you an update tomorrow.