how to analyse complex Data in Excel

hi
I have some tables in Excel and have to link for analysis. I do not know how i can analyse because i am raw in excel. so need help guys.

Here are three tables you can see. One is Demand forecast, second one is availability forecast and the lower one is difference analysis.
Now I want to show here those projects using the same resources. For example you can see in August, requirement of resource Y is more than availability. Therefore it is red. Anyhow I want to show only these projects( A,B,C etc) in august which are using Y. And same for other red items.
In separate table using some kind of formula or anything which I can handle.

I know it’s a huge question. But I need help.

regards
birkhe

sorry guys
i forgot to attach the example file.
here you find the file. Please have a look and help me.

birkhe

hi shg
Did you look at my problem?

birkhe

hi,
thanks for your quick reply. Thats a way of showing what i wanted. But i would like to inform you that i have 600 items (projects) in column and 24 months in row. If i apply this way then i can not identify or select only those projects in particular months. or can i by applying filter or ?
As i already told you that whenever requirement is more than availability in particular month for particular resource then i should make it somehow clear that these projects are using same resources. If i color it then it will show the same color in every column which can confuse.
So please help me to select only those project using same resource in particular month probably using some filter or something else.

regards
birkhe

hi shg
can you look at my problem please. You gave me exactly what i wanted but the color makes me confuse. Is there any way only to filter these project using same resources in particular month having color?
wating for your help and response.

birkhe

hi,
I have a table contaning huge data.
In first column i have project name and in second resources requirement.
In further table i have total resource requirement and availability.
I found a way to color those projects which are using same resources in the same month.
In main source data yello color represents those projects which are using same resources.
But the main problem is if there many projects require more resources than available it will automatically color as yello. Then it is confusing. I have around 15 types of resources ans if projects need more than availability then all columns will be colored yello which will be a mess.
so can any one help me to sort these projects which are underresourced and using same resources in same month using some filter or coloring ?

Birkhe

I have huge data and i want to put color in some conditions. When i put the condition and copy in to whole data then it copies the value not the color format. so how to do that?

Birkhe

I have huge data and i want to put color in some conditions. When i put the condition and copy in to whole data then it copies the value not the color format. so how to do that?

Birkhe

If the colours are the result of conditional formating you just copy and paste special formats. Is that what you mean?

Regards

Dav

Actually i have huge data sheet. Therefore i want to format cells with some conditions.
In my table if there deficit in particular month for particular resource then i want to color those projects using the same resources.
For example in Jan there is deficit of resource X so i want to show those projects using resources X.
One of my friend suggested to use that formula (i have applied in celland you can see in table) but when i applied its not working and if i copy it then it copies only the values not color.
so can you help me. i am raw in excel.

birkhe

hi
i tried the same formula in my sheet but its not working . I used the formula in format but its not working. so any special function should i do?

regards
birkhe

well if you change the conditional format in cell c4 to

Please Login or Register  to view this content.

I think it is what you require, but I would chnage the font colour in the conditional format to automatic as with both being yellow you can not read the value.

it can then be copied as the previous suggestion

Is that what you require

Regards

Dav

hi
thanks for reply but this formula is not working as well. I changed the formula but it says it contains errors.
so can you please resend me that excel file where you have changed the formula?
ya i noticed the font color.
and one further question. If there are many deficit with many resources then all the cells will be yello. Can we define different colors for different resources ti identify the projects easily?

regards
birkhe

hi
its working. I put semi colons instead of comma then its working. I do not know why comma was not working.
As i asked earlier in some months whole columns become yello because there deficit in each resource but can't we define something different to differentiate different resources so that it will be easier to identi
thanks

regards
birkhe

yes we could, but how many resources are there and which version of excel are you using?

Conditional formating only allows 3 conditions, so 3 different colours in excel up to 2003. In 2007 you can have many more than this.

Or you go down a macro approach to apply the formating

Regards

Dav

hi Andy
Is it a Macro or something else? I am not very much familiar with macro and evev if i change something in source data then it does not change the color and other data as well.
If i change resource requirement in each month then total sum is not changing.
Is the macro only way to do that? And i have 15 type of resources it means i should write macro for 15 colors?

birkhe