Help with Conditional formatting to use arrows based on Traget that changes everyweek

i think you should be able to use an index/match formula to look up WS1 and compare the weeks
And then WS3 with WS2
you would need to do this for each sheet

it maybe possible to be clever and use a indirect function to get the name of the sheet and the last 1/2 digits to get the number of the week and then take off 1 - so that the formula is the same for all 52 sheets
problem would possible be comparing ws1 with Ws52

BUT

to help and provide a formula we need to see the structure of the data
Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

I have used this formula
=IF(INDEX('WS1'!$F$7:$F$11,MATCH('WS2'!B7,'WS1'!$B$7:$B$11,0))>F7,3,IF(INDEX('WS1'!$F$7:$F$11,MATCH('WS2'!B7,'WS1'!$B$7:$B$11,0))<F7,1,2))

which will look up the value and do the conditional formatting

comparing the ranking ONLY - is that the comparison you want
OR
did you want to compare the % budget numbers rather than ranking

Also i need to think a little longer about how to change the formula so it works on all sheets rather than you have to modify and enter

here is the sheet for WS2

That would just mean changing the F reference to E and the < to > and < to >
=IF(INDEX('WS1'!$E$7:$E$11,MATCH('WS2'!B7,'WS1'!$B$7:$B$11,0))<E7,3,IF(INDEX('WS1'!$E$7:$E$11,MATCH('WS2'!B7,'WS1'!$B$7:$B$11,0))>E7,1,2))

BUT that will change the figures completely when compared to the ranking
see attached new column H in WS2

will the range be identical on all Sheets - as with indirect, that will be fixed and so if it changes on sheets , that will all need to be retyped anyway

once/if i get it to work
got the sheet name and can take 1 away and reconstruct - so i can get the previous sheet name OK - just cant get the indirect to work right now
BUT
before continuing would be good to know the way it will be used ?

try this formula

=IF(INDEX(INDIRECT("WS"&MID(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)),3,2)-1&"!E7:E11"),MATCH(B7,INDIRECT("WS"&MID(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)),3,2)-1&"!B7:B11"),0))<E7,3,IF(INDEX(INDIRECT("WS"&MID(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)),3,2)-1&"!E7:E11"),MATCH(B7,INDIRECT("WS"&MID(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)),3,2)-1&"!B7:B11"),0))>E7,1,2))


I have added to all sheets here

No error handling though
so #N/A or #REF may occur

i have also not considered
wk1 compared to wk52 or 53

your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here