Conditional Formatting

[jcorbin69993]

Hi,

I'm an account manager and I've come up with a spread sheet (contact list) that list all my clients, what they buy and when their renewal dates are. I have several sheets broken into product/service categories and on each sheet it lists all my clients that take those products/services and when they're due for renewal. This will help me create a quarterly contact list so I know who I need to contact at specific times in the year. I would like to add a further 4 sheets named 'Contact list Q1', 'Contact list Q2', etc. I would then like 'Contact List Q1' to pull information from the product/service sheets that are labelled January, February and March.

Is there some sort of conditional formatting that will help me achieve this?

I appreciate any help any of you can give me.

Thanks in advance,

Joe

[Pepe Le Mokko]

Hi
without seeing your data it's difficult to say. You could create a Pivot Table using consolidation ranges for each quarter

[jcorbin69993]

Hi
without seeing your data it's difficult to say. You could create a Pivot Table using consolidation ranges for each quarter

Thanks for your reply. I've linked a copy in my dropbox account for your review. I don't use excel frequently and so I'm not that advanced. I'll try your pivot table approach while I await your response.

Thanks

link removed by user

[Fotis1991]

In A2 and copy down and across this ARRAY formula will do the job, but will kill your pc..

=IFERROR(INDEX('Tacho Contracts'!A$2:A$10000;SMALL(IF('Tacho Contracts'!$C$2:$C$10000={"Jan";"Feb";"Mar"};ROW('Tacho Contracts'!A$2:A$10000)-1);ROW('Tacho Contracts'!A1)));"")

[jcorbin69993]

Good thing I'm on a Mac then .

Are you saying to copy this into cell A2 on worksheet Contact List Q1?

It doesn't like it. "The formula you typed contains and error".

[Fotis1991]

Yes. And..1) Use control+shift+enter(not just enter) to confirm--i don't know for mac.

2)replace all semi colons in my formula to comma.

[Fotis1991]

Did you see my example? Here another one for Excel 2003

[jcorbin69993]

Both suggestions above have not worked. Transferred over to a Windows XP Dell, the Ctrl + Shift + Enter didn't work, I got the same error. Changed to semi-colons and the error is now "the formula contains unrecognised text".

[Fotis1991]

Conditional Formatting ..??

[jcorbin69993]

I assumed what I wanted would be a function of conditional formatting. Obviously I'm wrong!

[Pepe Le Mokko]

It would be safer to post your sheet on the forum. Outside links might not be secure and are often blocked by administrators. Personally, I never open them

[jcorbin69993]

Fair enough. How to I attach to the forum?

[jcorbin69993]

Not to worry. It's attached now.

[Fotis1991]

I ask one more time.

Did you opened my attachments?

[jcorbin69993]

Yes I'm looking at it now and as far as I can see there is not the formula in cell A2 you sent. It looks as though it has been copied over. Sorry for being a newbie.

[jcorbin69993]

So I can see that all the information from one of the worksheets is now in the Q1 worksheet. However, looking at the cells there are none that display the formula you gave me. Sorry for being such a newbie

[Fotis1991]

Not all the informations, Only informations for the 3 first months(Jan,Feb,Mar) ! Click on A2 . In formula bar you must see the formula.

[jcorbin69993]

It only shows the information in the cell. There is no formula.image001.jpg

[jmitchell@smwc.edu]

Conditional format could certainly color code them on the existing sheet; however, this sounds more like using a nested "IF" statement would work.

[Fotis1991]

Must be something wrong with your computer settings. I attach a photo of what i see.

[jcorbin69993]

I have an older version of Excel. In the conversion process the data changes from what I want to #NAME? I'm running 2003 11.8346.8341

[Fotis1991]

My example in my previous post works fine even in a computer that we have in the office and use Excel 1997!!

[jcorbin69993]

My example in my previous post works fine even in a computer that we have in the office and use Excel 1997!!

Well it has to be this old piece of rubbish dell that my company refuses to update. Thanks for the formula, I'll remote access another PC at the office and try on there.

Thanks

[Fotis1991]

ok! ..............

[Martijnvc]

The ARRAY function indeed slows down the file considerably, while only one sheet has been included. Why not use Pivot Tables instead? Select all data in your datafile and create a pivot file. See attachement as an example. With the filter values you can filter for the months and contacts you want to show (or copy the sheet and filter on each sheet as required)

PS. Personally I would use only fake data when uploading your workbook, if possible. If you're an account manager I cannot imagine your boss being very happy about having all your client/ supplier data out in the open like this?

[Pepe Le Mokko]

Funny , it takes 25 posts to come back to post #2

[Martijnvc]

indeed. Hopefully including an example of what it will look like will be convincing as your #2 post seems largely ignored for the following 23 posts, for no obvious reason. Perhaps there is one, jcorbin?

[jcorbin69993]

I did mention that I'm a newbie and don't understand pivot tables yet. I was hoping for more from the author of post #2. An example would be great.

[Fotis1991]

indeed. Hopefully including an example of what it will look like will be convincing as your #2 post seems largely ignored for the following 23 posts, for no obvious reason. Perhaps there is one, jcorbin?

What is jcorbin?

Just to clear up the things here.

In fact this thread started in post#9. In this post if you notice you will see that i said:

..In A2 and copy down and across this ARRAY formula will do the job, but will kill your pc..

The reason is obvious. OP could not understand the solution. So in this case i had 2 options...

1) Ignore him and let him alone with his problem...

2) Try to explain how to use the formula. Actually try to help him to find a solution.

So that's it! AM i missing something??

[Martijnvc]

Well then look 3 posts above your last one and tell us what you think

[davemon]

Here is a solution using macros. If you enter "Q1" in cell O2 on the Tacho Contracts page, it will populate the Q1 list, "Q2" in cell O2 will populate the Q2 list and so on. The macros are good for 1500 lines of data on the Tacho Contracts page. As you add data to the Tacho Contracts page, just rerun the macros, and the data will be updated.
This won't tie up your processor(s) as long. When you open the workbook, you will have to "enable macros".

[Martijnvc]

Take it easy, no harm intended Fotis. you misread or misunderstand my post, set aside wether it is my wording or your interpretation

I only meant that, from what topic starter JCorbin describes as I understand it, the most logical means to an end (Pivot Tables) does not get worked out any further. Therefore I suggested the same as Pepe le Mokko in the #2 post in this thread, including an example which may give JCorbin an idea of what the Pivot Table solution can do. I asked if there is an obvious reason not to pursue that solution as I am suprised there was no reaction to Pepe le Mokko's suggestion.

In the meantime JCorbin explained Pivot Tables are also new for him/ her. Though the question might seem a little sarcastic, it was an honest question. Hopefully JCorbin had a look at the Pivot Tables and any other solution mentioned in this or any other thread enabling him/ her to reach his/ her goal in an optimum way.

[Fotis1991]

Take it easy, no harm intended Fotis. you misread or misunderstand my post, set aside wether it is my wording or your interpretation


In the meantime JCorbin explained Pivot Tables are also new for him/ her. Though the question might seem a little sarcastic, it was an honest question.

True is that English is not my strong card ...although I detest sarcasm;but in this case i just accept this..

...Though the question might seem a little sarcastic, it was an honest question.

..and there is no reason for me to continue on this issue.This issue is ended!