colomn conditional formatting ( I think!)

[Tradesman]

Hi Guys,

I had a problem solved by Moo yesterday-superb.

Can this be achieved in excel

I use week numbers ! eg 1 - 52

If I add a week number in a cell - say A1

If B4 -B56 are week numbers 1 - 52

How do I programme so the appropirate colomn is highlighted - say in grey with black boxes

Also If I then want to add the next number of colomns up from the week number in A1 (eg in the same row)-whats that eg week number 15, no. of weeks to add 10 eg adds week no.s 15+16...+24

and to make it easy to read weeks 15 is formatted as are the weeks to the end of the period (eg here 10 )

[Moo the Dog]

Tradesman,

See my attached sheet.. is this what you are looking to do?

- Moo

[Tradesman]

Thats it except Ive got my numbers twisted I meant the same row so the colomns would,be the week numbers.

Could we also match it to a code so if I typed abcdef into a cell eg it matched a table and then added the rows (sorry colomns basically what you did switched 90degrees) from the match.

Im copying and pasting conditional vlookups and youve shown me theres a different way!

[Moo the Dog]

OK, I have attached a new sheet. Tried to go off of what you were describing. Let me know if it is correct.

- Moo

[Tradesman]

thats exactly it, and Im guessing if I wanted to make the 10 week a variable I just edit

=IFERROR(SUM(OFFSET(A1,MATCH(A11,A2:A8,0),MATCH(A12,1:1,0)-1,,10)),"NO")

to

=IFERROR(SUM(OFFSET(A1,MATCH(A11,A2:A8,0),MATCH(A12,1:1,0)-1,,@@)),"NO") where @@ becomes the cell with the variable

Moo your spot on - nicely done and clean - Ill have anther question later but for now - its put the kid to bed time!!

[Tradesman]

Just one thought before i enter the war zone -- how did you get the grey area - conditional formatting??

[Tradesman]

I can see the conditonal formatting but dont get how it can only be relevant to the result??

[Moo the Dog]

Tradesman,

Yes, to adjust the # of weeks SUM'ed, you could add a cell that holds that variable. For instance, if you put the variable in cell A10, then you would change the 10 to the cell reference: A10, just as you stated above.

The grey area is, indeed, created by conditional formatting. I highlighted all of the cells from A1:BA8, then applied the following conditional formula:

Please Login or Register  to view this content.

If you want to add the variable range, the conditional formula will also have to be changed, with the 10 changed to the same cell reference as in the main formula on the sheet (in my example above, A10)

- Moo

[Moo the Dog]

To answer your question of relevance to the result with the conditional formatting, the formula is looking for three conditions, all of which must result in TRUE, due to the nature of the AND operator.

1st criteria: B$1>=$A$12... checks to see if the number in B1, C1, D1, etc. is greater than or equal to A12

2nd criteria: B$1<=($A$12+10)-1... checks to see if the number in B1, C1, D1, etc. is less than or equal to 10 more than your starting week, which is stored as a variable in A12. Then had to subtract 1, otherwise it would look for the 11th column too (1+10=11). And since we only wanted 10 in the original example, had to remove the last column.

3rd criteria: $A2=$A$11... checks to see if the row in A2, A3, A4, etc. is equal to A11

The formatting is applied only to those cells where all 3 conditions are TRUE.

Hope that helps explain it!

- Moo

[Tradesman]

So when I apply the following it doesnt work?

=AND(B$1>=$A$12,B$1<=($A$12+A14)-1,$A2=$A$11) - where A14 is now the variable no. of weeks to grey

and change the sum function to this

=IFERROR(SUM(OFFSET(A1,MATCH(A11,A2:A8,0),MATCH(A12,1:1,0)-1,,A14)),"NO")

I must have done something basic to the conditional formatting as it doesnt work

Sorry - EDIt - it only seems to work if I make it this?

=AND(B$1>=$A$12,B$1<=($A$12+$A$14)-1,$A2=$A$11)

Is this as conditional formatting only refers to unique cells?

Easily explained moo

[Tradesman]

Heres a clever addition- most analysis is done on rolling years - eg the week number rolling from 52 to 1 ( at the change of year )

Is their a work around? I use a rolling forecast from one area- my rolling forecast then do a comparison - If it doesnt roll forward someway I have to copy and past week 1 ,week2 etc after week 52

This has always been a hassle-any ideas to amend this or is this a seperate problem I hould start on a seperate thread

[Moo the Dog]

Oops! Yes, with the conditional formatting, the cell that holds the variable # of weeks (A14) needs to be an absolute reference $A$14 since the cond. formatting routine looks at each cell and needs to compare it to a stationary reference. Sorry!

The formula that handles the sum will work with either A14 or $A$14.

Regarding your rolling list from one year into the next, how is your data set up? If you have an example workbook you could upload I could take a look to see how to get it working for you.

- Moo

[Tradesman]

Thtas the nub of it- The spreadhseet i sue to plan stock for a particular client has ballooned into a beast which I think i will need to redo-hence my interest in learning more about this.

It views;
clients forcast ( not very reliable)
my forecast ( more reliable)
inbound stock
my stock and offers a view going forward of where we will be
so we know what we should order..working on lead times and what stock level i want

I think I might remap back to 2 sets of data-at the moment O delete off a colomn ( eg a week ) when I move into a new week
This means that one drops off the end so at some stage I have to add many vlookups-which is a PITA!

as you can see it needs streamlining pulling data in from many different tabs-thats the new one I added the indivual product review( with your help thank you!)

[Moo the Dog]

Looking at your sheet, it looks like currently you are starting your data at week 46, then rolling through the year. I'm guessing that this changes to start at week 47, then 48, etc. as the weeks go by.

The SUM formula will actually work fine to go past week 52 to week 1 of the following year {in your workbook}, because it isn't looking at the week # for that calculation, it just finds the column of the week # you want to start at, and SUMs from that cell to however many weeks (columns) you entered as the variable. You would just have to make sure that you don't have a duplicated week # in the series of weeks (ex. starting at week 46 on the left, then having another week 46 at the far right end of the data, otherwise you'll have issues.

Regarding the conditional formatting, I'm sure there are options to have that work with rolling over years... I tried

Please Login or Register  to view this content.

and it does work on the rollover, but it also highlights cells that don't need to be highlighted. For instance, if you wanted to start at week 42 and highlight 15 weeks, it would highlight weeks 42 to the end of the list, as well as the cells at the start of the entire list from the beginning of the year - which you wouldn't want. Those cells wouldn't be part of the SUM anyway.

There are usually a few different ways to accomplish the same task in Excel - and this Forum is a great place to learn them!

- Moo

[Tradesman]

Cheers moo- Ill have a play with that formula,

is there an easy way to add a button wioth a macro on it that would
a) remove week 46 and add it to the end -this would in fact make it rol forward
b) import the new csv files onto the relevant tabs ( I have found that elsewhere and am trying out stuff )


yes this is agreat palce- and I used to do programming when I was very young ( machine code ,....that dates me! 6502 processor!!) but life changed my direction-liking vba and macros and exel functions-I love the way you can solve problems in the real world with this stuff!