Check out some similar questions!

Pajoooo · Apr 17, 2011, 02:51 AM

View Attachment!
When you change the month or year in BF1 or BF2, the table is changing yellow and green fields that show my siht. Sunday is not a business day. I need macro that will in each painted column, except sundays, random insert seven tracks from column B (1-75). Random inserted songs in column may be marked with "black circle symbol". Plus, song under the serial number of the 67th must be in each painted column. (eighth)

JBeaucaire · Apr 17, 2011, 06:41 AM

The formula in Q13 should simplify down to:

=CHOOSE(WEEKDAY(Q14),"Su","Mo","Tu","We","Th","Fr" ,"Sa")

Q14: =DATE(BF2,MATCH(BF1,monthNames,0),1)

Not sure what you're wanting, I don't see any example "results".

Pajoooo · Apr 17, 2011, 11:28 AM

Sorry, example "results" for April in Attachment.

JBeaucaire · Apr 19, 2011, 02:59 AM

Woo, this was not a trivial exercise at all.

I decided to go the non-macro route. I've added some grayed out columns on the right and some grayed out cells at the top that "help" the formulas in your table now give you a mostly random list each time you press F9 to calculate the sheet. Auto-calcs are turned off on this sheet, you need to press F9 to get new calcs.

Be sure NOT to copy changes you ever make in the formulas over the row for song 67 since that one is set to be "on" all the time, as you requested. Therefore the formulas then grab 6 other songs.

Pajoooo · Apr 19, 2011, 03:45 AM

I never doubted your ability and willingness to help me.

Very, Very thanks!

Pajoooo · Apr 20, 2011, 01:25 AM

If you do not take away too much time, praying for one more service:

In the example you have done, the conditional formatting is: =MOD(Q$14;4)=0 and =MOD(Q$14;4)=3.

How do I get this same thing with conditional formatting:

1. =MOD(Q$14;4)=3 and =MOD(Q$14;4)=2

2. =MOD(Q$14;4)=1 and =MOD(Q$14;4)=0

3. =MOD(Q$14;4)=2 and =MOD(Q$14;4)=1

or how to set up a already existing example.

JBeaucaire · Apr 20, 2011, 05:55 AM

That conditional formatting was already in your workbook. You already see the conditional formatting setups, so I'm not sure what you're asking.

There are two conditional formatting rules already in place, you can add one more for a total of 3 if you wish.

Pajoooo · Apr 20, 2011, 12:55 PM

No, not in the same workbook.

These are the four sihts with different workdays in different workbook. Select Q15 and change Conditional formatting rules, for green filled fields to =MOD(Q$14;4)=2, and for yellow filled fields to =MOD(Q$14;4)=1. Collored fields move. How move randomly selected songs in the same time.

JBeaucaire · Apr 22, 2011, 05:55 PM

No idea what we're talking about here.

Pajoooo · Apr 23, 2011, 01:35 AM

In the example you sent me are two conditional formatting rules:

=MOD(Q$14;4)=0
=MOD(Q$14;4)=3

and everything works OK.

I need for different shift to change these rules to:

=MOD(Q$14;4)=3
=MOD(Q$14;4)=2

OR

=MOD(Q$14;4)=1
=MOD(Q$14;4)=0

OR

=MOD(Q$14;4)=2
=MOD(Q$14;4)=1

What should be changed other than these rules that everything would be OK. Each of these 4 setups would work in a separate workbook.

JBeaucaire · Apr 23, 2011, 12:32 PM

Originally Posted by Pajoooo

Each of these 4 setups would work in a separate workbook.

So what's wrong with your separate workbooks if it works?

Pajoooo · Apr 23, 2011, 04:25 PM

See in attachment what happens with Randomly songs if conditional formatting changed to:

=MOD(Q$14;4)=3
=MOD(Q$14;4)=2

What is wrong?

JBeaucaire · Apr 24, 2011, 12:37 PM

The formulas (each group of 4 cells is a different formula) are constructed specifically to match the MOD() functions from your original sample. To be able to pick ANY mod values would add a significant level of complexity above what you've already done.