automatically fills the cells where the test needs be be done

Hi,

i'm currently setting up a new schedule for some tests I need to perform in different sequences. What I ant is that the file automatically fills the cells where the test needs be be done. i.e lets say test number one need to be done every second week of the month, than the cell in every second week of the month should have automatically a color. But if I change the frequency to quarterly than this should change automatically in the file.

who can help?

Regards,
Leon

Title amended

Perhaps something like "automatically fills the cells where the test needs be be done"
Are you still using XL2003? If not please update your profile. Thanks

You don't appear to have mocked up your expected outcomes (results) in the attachment - you need to do this manually to show us what you are aiming to achieve.

I cannot work out how you have decided which week to choose - you are going to need to properly explain where the shading is so that we can suggest a formula for the CF rule.

But the 1s are not calculated by a formula - how are they going to get there? This may all be completely obvious to you, but it isn't to me, sorry.

The bits in green don't seem to follow what you have just said, either ...

Excel 2016 (Windows) 32 bit

	Y	Z	AA	AB	AC	AD	AE	AF
2					1
3					1
4
5
6
7	Frequency			January
8				1	2	3	4	5
9	Monthly
10	Monthly
11	Monthly
12	Monthly
13	Monthly
14	Monthly
15	Monthly
16	Quarterly
17	Quarterly

Sheet: analysis

I can't give a solution if it's not clear what you are trying to do. Your sample data MUST be an accurate reflection of what you want and your current data layout. I cannot be expected to decide in which week the payment should fall!!! Don't expect a solution with a half-baked brief.

What about annually? And how will Excel know when to stop highlighting January and move to February? Or do you want all instances across the year highlighted? There are still gaps in your brief - you need to give the whole picture, please, otherwise I'll give you a solution and then you'll come back and tell me it isn't quite what you want because of x, y or z that you failed to mention before.

And annually???

I am running short of time now - hopefully someone else can piuck this up and run with it.

OK - so how are the 1s getting there? Or are they there simply for us to see where you want the shading?

OK, so in that case, why is the quarterly payment in week 5 of January? What is Excel meant to use to calculate this start point? The quarterly intervals are not entirely clear.

OK - I get that you don't think my questions matter, but I am afraid you are wrong. When you have provided a sample sheet that accurately reflects what you REALLY want, I'll have another look. Please be clear: it is not your helper's job to work out the rules for you. This is part of your requirements, so you need to think about it and come up with something concrete (e.g. every 13 weeks starting with week 4 for the quarters).

I am afraid I have run out of time for the moment - it has taken almost two hours so far to prise very basic requirements out of you and I am afraid I need to move on to other things. Sorry. Hopefully someone else will step in.

That's a completely unfair statement to make. Of course I want to help - I would not have spent over two hours with you on this if I didn't. It's disrespectful to suggest otherwise and does not encourage me to help you further.

how difficult is it to move one of the numbers....???

Move it where, though? You are still expecting me to decide where exactly your shading should go for you, and that's not what I am here to do. YOU need to decide on the exact criteria and then somebody here can help you with a formula to achieve that.

I explained already where the shading should be

Yes, for monthly and annually, but quarterly is still unclear.

So are you saying that you want the quarterly shading in the last weeks of January, April, July and October?

Is this correct ?

Just use Index / Match from frequency and above rows.

Regards.

OK - thanks. That is now clear.

The problem you have with your layout is the merged cells for the months - are you happy with a helper row?

Originally Posted by menem

Is this correct ?

No - the quarterly shading is in the wrong place. See the discussion over the last few posts. And the 1s are only there as a guideline to us - they won't be there in reality.

No, it isn't!

The quarterly shading is NOT what you have just told me you wanted and the solution uses the 1s that won't be there in the real file.

OK - I give up. Good luck with this!