Replies

Well I got lucky and found an old post and formula from Hans that put me on the right track and I solved my own problem. I am re-posting my fixed file in case someone could use the formula.

Thanks to anyone who may have started working on my request.

yoyophil

Well I got lucky and found an old post and formula from Hans that put me on the right track and I solved my own problem. I am re-posting my fixed file in case someone could use the formula.

Thanks to anyone who may have started working on my request.

yoyophil

Are you putting the word ABSENT in place of a grade? If you are then try this adjustment to Kieran’s formula. It will look for the work ABSENT first.

=IF(OR(L7=”Absent”,N7=”Absent”,P7=”Absent”,R7=”Absent”,T7=”Absent”,),”Absent”,IF(OR(ISBLANK(L8),ISBLANK(N8),ISBLANK(P8),ISBLANK(R8),ISBLANK(T8)),”Absent”,IF(MIN(L8, N8, P8, R8, T8)<40,"FAIL","PASS")))

yoyo

Are you putting the word ABSENT in place of a grade? If you are then try this adjustment to Kieran’s formula. It will look for the work ABSENT first.

=IF(OR(L7=”Absent”,N7=”Absent”,P7=”Absent”,R7=”Absent”,T7=”Absent”,),”Absent”,IF(OR(ISBLANK(L8),ISBLANK(N8),ISBLANK(P8),ISBLANK(R8),ISBLANK(T8)),”Absent”,IF(MIN(L8, N8, P8, R8, T8)<40,"FAIL","PASS")))

yoyo

Legare’s formula is fantastic but I think it needs to have some absolutes used or you may run into a few problems.

=IF(E2=”FEDIN1″,MAX(($A$2:$A$26=A2)*$G$2:$G$26)-G2,””)

just an observation.

yoyo

Legare’s formula is fantastic but I think it needs to have some absolutes used or you may run into a few problems.

=IF(E2=”FEDIN1″,MAX(($A$2:$A$26=A2)*$G$2:$G$26)-G2,””)

just an observation.

yoyo

I added to Steve’s formula and I admit there is probably a shorter formula that might work but if placed in E3, it works. You can copy it down. If you copy it across you manually have to change the month in the date ranges and then copy down. Maybe someone can fix that glitch.

=IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0)>=DATE(2004,5,1),IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:M”),13,0)<=DATE(2004,5,31),VLOOKUP($A3,INDIRECT(E$1&"!"&"A:j"),10,0),"")))

-yoyophil

I added to Steve’s formula and I admit there is probably a shorter formula that might work but if placed in E3, it works. You can copy it down. If you copy it across you manually have to change the month in the date ranges and then copy down. Maybe someone can fix that glitch.

=IF(ISERROR(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:J”),10,0)),””,IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:m”),13,0)>=DATE(2004,5,1),IF(VLOOKUP($A3,INDIRECT(E$1&”!”&”A:M”),13,0)<=DATE(2004,5,31),VLOOKUP($A3,INDIRECT(E$1&"!"&"A:j"),10,0),"")))

-yoyophil

Couldn’t you simply add conditional formatting which would highlight the new entry if it equals the cell to its left?

Seems simple enough unless I am missing something here.

-yoyo

I encountered this same problem a while back and could not find any available setting to rectify this. I did discover though, that if you put a space before the entry in the cell, excel disregards it as a hyperlink.

Thats the best I can do for you.
-yoyo

I encountered this same problem a while back and could not find any available setting to rectify this. I did discover though, that if you put a space before the entry in the cell, excel disregards it as a hyperlink.

Thats the best I can do for you.
-yoyo

I took your spreadsheet and adjusted it a little. I reformatted your entries and added columns that you can hide. It works as needed but may not suit your needs if you have to stick with the entries as you presented.

Hopefully seeing it is all the explanation you need if you wish to use the ideas and expand them into a larger spreadsheet.

I took your spreadsheet and adjusted it a little. I reformatted your entries and added columns that you can hide. It works as needed but may not suit your needs if you have to stick with the entries as you presented.

Hopefully seeing it is all the explanation you need if you wish to use the ideas and expand them into a larger spreadsheet.

This uses a hidden column for each FL worksheet and entry. The way I understand your problem, you are looking to add specific cells across all worksheets (in your example file FL1:FL2), but only if the corresponding Master sheet cell E13:E102 equals a 1 (Master sheet cell E13 is a 1 so add all results from sheet FL1). See if my spreadsheet would work for you.

yoyo-phil

John;

Thanks for the clarification. The formula works as needed. Thanks for the little lesson.

This board is fantastic.