Windows : Excel formula - apply to entire workbook?

Joe 90 · 23-11-2010, 11:44

So, I've got a formula to create alternate row highlighting:

=MOD(ROW()-2,1*2)+1<=1

Is it possible to modify that so that it would be applied to all worksheets?

I tried an ='A:Z'! but didn't know how to merge that with the existing statement.

Joe 90 · 29-11-2010, 16:03

Got another formula query....

In this worksheet I've got dates entered for the last time something was used (each thing with its own column). I want the column to do a check on each row & highlight the cell with the most recent date, with the others in a difference colour.

Anybody know how that can be done? I'm currently doing some Google base research!

semi-pro waster · 29-11-2010, 19:39

The second one sounds like you could do something with conditional formatting possibly but it depends on how exactly you are aiming to do it - if it's just dates more recent than a certain point then that shouldn't be too hard (as a guess without looking you could even set it to check for a particular month using the Month() command) , if it's a comparison of all dates in a column and then to highlight say the 3 most recent dates then it's going to be more difficult.

I don't actually have Excel at home, OpenOffice does what I need but if you want I'll have a look at work tomorrow and see what I can do. It'd be helpful if you can clarify slightly further what you want to achieve.

Joe 90 · 29-11-2010, 22:18

I sorted it

=AND(B2=MAX(B$2:B$19),B2<>"")
=AND(B2<>MAX(B$2:B$19),B2<>"")

whoop. Forgot how excel deals with dates. MAX FTW!

23-11-2010, 11:44	#1
Joe 90 Absinthe Join Date: Jan 2007 Location: Chester Posts: 2,345	Excel formula - apply to entire workbook? So, I've got a formula to create alternate row highlighting: =MOD(ROW()-2,1*2)+1<=1 Is it possible to modify that so that it would be applied to all worksheets? I tried an ='A:Z'! but didn't know how to merge that with the existing statement. __________________ 360 Blog \| Join GiffGaff \| Twitter

29-11-2010, 16:03	#2
Joe 90 Absinthe Join Date: Jan 2007 Location: Chester Posts: 2,345	Got another formula query.... In this worksheet I've got dates entered for the last time something was used (each thing with its own column). I want the column to do a check on each row & highlight the cell with the most recent date, with the others in a difference colour. Anybody know how that can be done? I'm currently doing some Google base research! __________________ 360 Blog \| Join GiffGaff \| Twitter

29-11-2010, 19:39	#3
semi-pro waster Provider of sensible advice about homosexuals Join Date: Oct 2006 Location: London Posts: 2,615	The second one sounds like you could do something with conditional formatting possibly but it depends on how exactly you are aiming to do it - if it's just dates more recent than a certain point then that shouldn't be too hard (as a guess without looking you could even set it to check for a particular month using the Month() command) , if it's a comparison of all dates in a column and then to highlight say the 3 most recent dates then it's going to be more difficult. I don't actually have Excel at home, OpenOffice does what I need but if you want I'll have a look at work tomorrow and see what I can do. It'd be helpful if you can clarify slightly further what you want to achieve. __________________ "Your friend is the man that knows all about you, and still likes you." - Elbert Hubbard

29-11-2010, 22:18	#4
Joe 90 Absinthe Join Date: Jan 2007 Location: Chester Posts: 2,345	I sorted it =AND(B2=MAX(B$2:B$19),B2<>"") =AND(B2<>MAX(B$2:B$19),B2<>"") whoop. Forgot how excel deals with dates. MAX FTW! __________________ 360 Blog \| Join GiffGaff \| Twitter