Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Can the text of the cell on one tab change color based on whether or not a match exists in a column from another tab?


Hi Experts,
I am working with two tabs: one that has the following format (customers down the left and yearly sales across the top)

Tab One
                       2000      2001     2002
Customer 1    $0.00     $0.00   $57.00
Customer 2    $250.00 $0.00   $457.00
Customer 3    $50.00   $10.00 $259.00
etc.

Tab Two

Tab One
                       Rep 1    Rep 2    Rep 3
Customer 1    $0.00     $0.00   $57.00
Customer 2    $250.00 $0.00   $457.00
Customer 3    $50.00   $10.00 $259.00
etc.

My question is this: On the tab with yearly sales, I want to identify which rep (based on color coded text) was responsible for the sale. So, for example, If there was a sale for customer 1 indicated on the first tab (yearly), the conditional formatting would check the second tab (sales by rep) for a match and color the text on the yearly tab based on which rep had the match.

The final output would then be seen on tab 1 (yearly) and look like this

Tab One
                       2000      2001     2002
Customer 1    $0.00     $0.00   $[57.00] column 3 would be blue text (represents rep 2)
Customer 2    $250.00 $0.00   $457.00
Customer 3    $50.00   $10.00 $259.00

It's difficult to articulate without demonstrating what I'm trying to achieve so I've attached spreadsheet with a sample of the data I'm working with. Any insight would be VERY much appreciated!

Thank you.


EE-Sample-Data.xlsx
0
DesignCityStudio
Asked:
DesignCityStudio
  • 9
  • 6
1 Solution
 
regmigrantCommented:
not sure I understand what you are driving at but maybe you can clarify:-

if more than one rep sells to a customer which colour would be used?

if each customer sees only one rep  the colour can be inferred from the customer?

0
 
DesignCityStudioAuthor Commented:
No problem; I know it's confusing.

The issue is that one rep may have inherited an account over time (reps leave, accounts get handed off to newer reps, etc.). The other issue is that I'm not able to run this report as a whole -- meaning, I can run the report w/ customer sales year over year OR customer sales by rep summary. Ultimately, I want to see both of those things in one report so if I could just color code the year-over-year tab (yearly) based on a match from the rep tab, I'd be golden.

Take the first row of data from the yearly tab for example:
#3002 Menards had a sale in 2002 for $57.00 (originally in black text). Now, the conditional formatting would change the color of that text based on which rep was responsible for the sale that year (very unlikely this would overlap and if it does, it doesn't matter that much since we just want to see when the account was handed off). So, in 2002, I can see that Rep 6 has a matched value ($57.00) so the text on the yearly tab would be changed to the color that represents Rep 6 (brownish orange in this case). Make sense?
0
 
regmigrantCommented:
You face a couple of problems - firstly conditional formats cant 'choose' the target format based on a value they are either true or false - so to change the colour for each rep you need a rule for every rep on every cell. Excel imposes a physical limit on the number of cells with conditional formats so you may find you are hitting that after about 200 sales (this is from memory it may be more).

Second and most important you are trying to infer a relationship between two different sets of data, the actual relastionship is between a Rep and a Customer for a given time period and if you could track that it would be a simple matter to decide which Rep applies for a given period. Accuracy would be improved if you had date of sale information as well.

In your sample data this is obvious from the sale of $650 in 2003 which has no associated salesperson but, for example, might be the result of 2 sales at $150 and $500 either from the same sales person or more than one - you dont have any way to decide which rep and therefore which colour to apply.

However I am guessing that you dont have much control over the matter so have had a stab at it based on these limitations. I've used different colour coding so you can see it working but the principles are obvious. When you are changing the formats to what you want, or adding extra sale reps you should select the first cell (C2) make all the changes there and then 'paste format' across the rest or excel will tend to screw things up as it tries to apply the various cell references.

I've also left a table in there which might help you understand the process, and the problems a little better, its an ugly formula because we are inventing the relationship but it works for the sample as is.

Let me know what you think
Reg
EE-Sample-Data-1-.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DesignCityStudioAuthor Commented:

Good morning Reg,
I know the data is from ideal (thank QuickBooks for that one) but you managed to identify things in a way that I was aiming for! I do have a question (probably a silly one to the experts in this field) but let's say I like the outcome of this formula (and I do) and want to use it now with my live data...

I tried adding two more lines of data (on both tabs of course) and then paste the formula but I ended up with all -2's even though there are matched sales. In other words, I think this will do the trick as soon as I determine how to apply it correctly [sorry]..

Thanks, Reg.
0
 
DesignCityStudioAuthor Commented:

P.S. Maybe the bigger issue (or the thing I'm missing) is that there is no formula in C2 - the formulas in the worksheet you sent me has the formulas in your table -- starting with C10...
0
 
regmigrantCommented:
Hello Design :)

the formula is used in  'conditional format' - sorry I took it from the way you phrased the question that this is what you were looking for.

To apply it to new data:- got to C2, choose paste format (the paintbrush) then select all the new items and it will copy the formatting onto the new items. (if you can't find the paintbrush use 'copy', select the new data, 'paste special', 'formats'

to edit (which you will need to do to add the rep details)
Select C2, find 'conditional formatting' - this depends in which excel version your running but its on the home tab of the ribbon or under 'Format, conditional formatting' if I remember correctly
Ive used the 'use a formula to...' option and you should see the two rules I setup for Rep 6 and Rep 3. edit one of the lines (select with mouse and Ctrl-C), cancel, select 'new rule' and paste the formula back in (ctrl-V), then modify the 'rep' and choose a new format. When you ok the dialog it will be applied only to C2 - follow the instructions above to paste it onto all the other fields.

As an aside - Quickbooks has many reports and I'm sure there will be one that can be modified to get you more complete data - if you have the payroll option its under commissions- but there's also so revenue by sales person. you could also maintain the customer/rep information in excel and use it to improve the extracts

Good luck!
Reg
0
 
DesignCityStudioAuthor Commented:

Hi Reg,
I don't have any idea why but when I try to include additional rows of data, the conditional formatting stops working... I've even changed the named range for "repsales" and no luck. I'm going to keep plugging away at this and see if I can figure out the trick. I'm actually rather technical in other areas so I'm not sure why I'm having such a problem with this [ack]. Anyway, it's definitely working exactly how I wanted it to on the three rows of data we started with so if I haven't worked it out by the AM, I'll officially accept.

Regarding QuickBooks -- Yes, it's tricky that one. You can run Year-over-Year data OR Sales by Rep (with or without detail) but it's challenging to see account inheritence in one export (hence the color-coded conditional formatting). Anyway; off to keep trying!

Have a lovely afternoon/evening,
Design {aka Kate)

Cheers
0
 
regmigrantCommented:
Hi Kate

Not sure which version of Excel you are using but I know its a pest in 2010 - the user interface is from the ark and because we are applying a rule which covers a lot of other cells and some need to be relative and others fixed it get confused. Thats the reason I suggested applying all the rules to one cell until it works then pasting the format to copy it to all the others, it took me several attempts as well and I only got it to work by deleting all rules and recreating when I needed to make a change other than adding a new 'Rep'

the area you (especially) need to watch is 'applies to', The best way I can think of is to define a name that covers all the data you want to apply the rules to and put "=<insert your name>" in the 'applies to field' and then ok it. The worst problems seem to come if you try and change "applies to" whilst also modifying the rules themselves so do it as two separate operations (or use paste format).

If you can't make it work send me a copy of the workbook and I'll take a look, or if the data is sensitive let me know how many rows are needed and which version of excel you are using and I'll try a different option

I'll see if I can find my old copy of quickbooks from a previous life - which version do you run?

Reg (aka Greg :)
0
 
DesignCityStudioAuthor Commented:
Mornin' Greg,

I'll be honest, I walked away from fighting with the spreadsheet on Friday and decided not to revisit it until this morning :) I never did get it to work... I'm using Excel 2010 but maybe I'll try opening it in 2007 and see if that helps. Part of the problem, aside from asking Excel to work magic [grin] is that I am working with a sizable amount of data - 2,791 rows. As much as I'd like to pass it along, the data is fairly sensitive to some. However, I can't get it to work w/ 4 rows of data ( 3 yes, 4 or more - not so much) so, like I said, I will try it in 2007 and see what happens.

QuickBooks Enterprise 11.0 Manufacturing and Wholesale is the culprit on that end...

Stay tuned ;) And thanks, Greg!
0
 
DesignCityStudioAuthor Commented:

Hmmm... Oddly enough, when I bring up your example in 2007, your formulas aren't working either. They do in the table you built to demonstrate the logic but the conditional formatting stopped working entirely (stripped out the rules). After attempting to re-enter the rule(s), Excel flagged me saying I could not use conditional formatting when referencing another tab or a worksheet. Back to 2010 :p
0
 
regmigrantCommented:
Morning- or whatever greeting is appropriate to your timezone - Kate

It is difficult to ge this in a state thats easier to manipulate but I have got a bit closer with the attached I hope.
I've extended the list to 3000 rows and modified the formatting to apply to that range
You can cut and paste your actual data set into place
I've also added a few demonstrative numbers, but not increased the rules so they still only cover reps 3 and 6.

Applying this logic has highlighted a few issues, largely around this problem of no real relationship being enforced so keep the following in mind:-
1. the rules assume that the customers will be in the same order and * on the same row * in each sheet, if there is an extra one somewhere on either sheet the formatting will fail beyond that (but at least you will have a clue where)
2. the formula uses a 'match' function which looks up the *first* matching value in a list so if you have two sales of the same amount by different reps the first will get the cigar every time. I can't see a way round this without knowing the date of the sale
3. There is no business like show business - so why do we do *this* for a living?
4 The problem of copying the format across the list is due to excel - and my explanation not being detailed enough. When you go into conditional formatting you should change the drop down to read 'This worksheet' not 'current selection' (the default) or you wind up with a lot of minor variations on particular cells and things get fusing pdq. Ideally you will wind up with one rule for each rep with an 'applies to' of $C$2:$Y$3005
5. It wont cope with obvious stuff like a reps sale values being split over a year end but all reported in one year for, say, commission purposes unless the Client Sale total has identical treatment
6. for the same reason it won't spot that a clients sale value might be made up of more than one sale- even if its for the same rep.

I hope that despite these limitations it proves of some use

Greg





EE-Sample-Data-1--2-.xlsx
0
 
DesignCityStudioAuthor Commented:

Greg,

All very, very helpful stuff! I am goign to give-it-a-whirl and touch back shortly. Meanwhile, I'm guessing you're in the UK? I spent three months over there a few years back - just outside of London. Good times... :)

Stay tuned... Kate
0
 
regmigrantCommented:
London is my favourite city - wish I had more time to appreciate it. Are you US based or antipodean?
Best of luck

Greg
0
 
DesignCityStudioAuthor Commented:
Although I was asking Excel to do something that it isn't designed to, Reg made it work for what I needed it for. Excellent level of ongoing support!
0
 
DesignCityStudioAuthor Commented:

Greg,
Yep... I'm a true Minnesotan living in the US of A. Thanks again for all of your help, I really - really appreciate it.

Cheers and Be Well in the World! - Kate
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now