Solved: Adding an extra range to formula?

[Simon Lloyd]

The formula below works fine

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops ,2,FALSE),"Not Available")))

But i need the formula to look like this (or something like it!)

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,$B$5:$V$5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops ,2,FALSE),"Not Available")))

however quite rightly it says i have too many arguments after i added the range shown in red, if however i choose $A$4:$V$5 it creates a circular formula and does not work, this formula when correct will be copied in to other cells on row 5.

Any ideas on how i achieve my goal?

Regards,
Simon

[Bob Phillips]

You have to test them separately Simon.

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE))+COUNTIF($B$5:$V$5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",
IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

[Simon Lloyd]

Ah!, the Great "El Xid",

Thanks for that mod!

Looking at your suggestion i will have to modify the formula for each cell i copy it to, either that or suffer the annoying "you've created a circular formula...." messsage.......being lazy i thought there would be a one size fits all!

Regards,
Simon

[Bob Phillips]

Can't you make the row (I assume row, it may be column) relative as against absolute?

[Simon Lloyd]

Can't you make the row (I assume row, it may be column) relative as against absolute?

Yes of course, it seems incompetency is my thing lately!, i should of thought of that then i only have 2 manual changes.

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE))+COUNTIF(B$5:V$5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",
IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

the row will stay absolute, and the only changes i need to make is the last cell it is copied to and the first.

Regards,
Simon

[Simon Lloyd]

Re-Think! it cant be done this way

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE))+COUNTIF(B$5:V$5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",
IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

Lets say the formula is in A5 i next copy it to C5 next E5 etc to V5 if i then use the formula i get a circular reference!

if i paste the formula in to C5 i now need to ignore that it looks at C5 and so on!

Any ideas?

Regards,
Simon

[Bob Phillips]

Simon,

I am not sure I get it.

If that is the formula in A5, what would the formula in C5 that you want be?

[Simon Lloyd]

The formula appears in A5, C5, E5, I5, K5, N5, Q5, S5, U5

The formula needs to look at the cells $A$$:$V$4 as well as the cells above, the formula is looking to see if the name is used anywhere else if it is "Already Assigned", the trouble is the formula becomes circula the way i have it so lets say for instance if the formula is in C5 it should look something like this

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE))+COUNTIF(B$5,VLOOKUP(B5,Ops,2,FALSE))+COUNTIF(D$5:V$5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

and in E5 look something like this

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE))+COUNTIF(B$5:$D$5,VLOOKUP(B5,Ops,2,FALSE))+COUNTIF(F$5:V$5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

etc.

Would you believe it?, i have just tried out my own suggestion as above and it works! needs more testing though!

Regards,
SImon

[Bob Phillips]

To be able to simply copy it it, I think it should be

=IF(ISNA(VLOOKUP($B$5:B$5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP($B$ 5,Ops,2, FALSE))+COUNTIF($B$5:B$5,VLOOKUP(B5,Ops,2,FALSE))+COUNTIF(D$5:$V$5,VLOOKUP( $B$5,Ops,2,FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP($B$5,Ops,2,FALSE))<>1,VLOOKUP($B$5 ,Ops,2,FALSE), "Not Available")))

in C5

[Simon Lloyd]

Bob, thanks again, i do understand why you added the absolute reference for B5 but that value of course (i say of course but i didnt explain that did i?) will change for each cell it is copied to, my typo and my apologies!, if the formula resides in A5 then it looks at B5, if it is in C5 then looks at D5 etc.

Regards,
Devoted Pupil!

[Bob Phillips]

I wondered about that Simon, but I saw that you had one $B$5 in it. More importantly was the

COUNTIF(B$5,VLOOKUP(B5,Ops,2,FALSE))

changed to COUNTIF($B$5:B$5,VLOOKUP(B5,Ops,2,FALSE))

and COUNTIF(D$5:V$5,VLOOKUP(B5,Ops,2,FALSE))

changed toCOUNTIF(D$5:$V$5,VLOOKUP(B5,Ops,2,FALSE))

which will enable copy-paste to adjust the formula correctly.

[Simon Lloyd]

For some reason Bob the formula still shows a circular reference if the name is found in part of the range to look at and you try to enter the name in a different part of the range that it looks at, but it should show Already assigned! i feel VBA looming quick....i think i may fill the second terms of your criteria!

=IF(ISNA(VLOOKUP(D5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(D5,Ops,2, FALSE))+COUNTIF(A5,VLOOKUP(D5,Ops,2,FALSE))+COUNTIF(E5:V5,VLOOKUP(D5,Ops,2, FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP(D5,Ops,2,FALSE))<>1,VLOOKUP(D5,Ops ,2,FALSE), "Not Available")))

This formula entered in to other cells and modified slightly to identify the range it should look at like this

=IF(ISNA(VLOOKUP(F5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(F5,Ops,2, FALSE))+COUNTIF(B$5:$D$5,VLOOKUP(F5,Ops,2,FALSE))+COUNTIF(F$5:V$5,VLOOKUP(F 5,Ops,2,FALSE)),"Already Assigned",IF(COUNTIF($H$29:$K$39,VLOOKUP(F5,Ops,2,FALSE))<>1,VLOOKUP(F5,Ops ,2,FALSE), "Not Available")))

this is how i have progressed to U5 if i only use one instance of the name in the range then its fine but if i try to enter another i get the circular reference even though the formula doesnt look at itself.

Regards,
Simon

[Bob Phillips]

Don't you dare. If you do I will disown you

This is what you want in A5, then copy and past across to C5, E5, etc.

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",
IF(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE))+COUNTIF(D5:$V5,VLOOKUP(B5,Ops,2,FALSE)),"Already Assigned",
IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

BTW, what is to stop me putting 1 in B5 and then in D5?

[Bob Phillips]

2:00 am?

[Simon Lloyd]

Don't you dare. If you do I will disown you

you wouldnt put a frightened puppy out would you?

Have a heart man!

BTW, what is to stop me putting 1 in B5 and then in D5?

thats the idea of the formula Bob, i dont want anyone to be able to do that, if the Ops index is already used and his name appears in the range then "Already Assigned" as he cant work two places at once!

2:00 am?

I'm on nights at the moment!

Thanks for that Bob i will try it shortly and post back.

Regards,
Simon
LOL

[Simon Lloyd]

Bob that is something similar to what i had but doesnt do the trick! lets take this formula in M5

=IF(ISNA(VLOOKUP(N5,Ops,2,FALSE)),"",IF(COUNTIF($A$4:$V$4,VLOOKUP(N5,Ops,2, FALSE))+COUNTIF(P5:$V5,VLOOKUP(N5,Ops,2,FALSE)),"AlreadyAssigned",IF(COUNTI F($H$29:$K$39,VLOOKUP(N5,Ops,2,FALSE))<>1,VLOOKUP(N5,Ops,2,FALSE), "Not Available")))

it also needs to look at A5:L5 like this

+COUNTIF(A5:$L5,VLOOKUP(N5,Ops,2,FALSE))

so in summary the formula is looking at all of row 4 up to column V for the Ops, then it must look at all of row 5 upto column V excluding the cell in which it resides if a match is found display "Already Assigned", and lastly it must look in the area $H$29:$K$39 if a match is found display "Not Available" if none of the above are true show Ops name.

My last posted formula when reformatted seems to read correct its just that damn circular reference!

Regards,
Tired!

[Simon Lloyd]

Bob, i have been playing around with Excel's options, i set the Iterations to 1 and it seemed to cure the problem - But - there's always a "But" i have some cells that do a simple sum what effect would this have on the worksheet?......is the iteration thing the way to go?

Regards,
SImon

[Bob Phillips]

Simon,

I wouldn't go the iteration route, that effectively turns off circular referencing checks, which might help here, but is a technique that should only be used to get speciual effects, you need to know what you are doing.

Here is another alternative, in A5 and copy across as usual

=IF(ISNA(VLOOKUP(B5,Ops,2,FALSE)),"",
IF(OR(COUNTIF($A$4:$V$4,VLOOKUP(B5,Ops,2, FALSE)),SUMPRODUCT(--(COUNTIF(INDIRECT({"B5","D5","F5","J5","L5","N5","R5","T5","V5"}),B5)))>1), "Already Assigned",
IF(COUNTIF($H$29:$K$39,VLOOKUP(B5,Ops,2,FALSE))<>1,VLOOKUP(B5,Ops,2,FALSE), "Not Available")))

The one 'problem' is that if you add a duplicate in row 5, the previous entry will lose the name and change to 'Aready Assigned'

[Simon Lloyd]

Bob thats quite a formula! the "problem" you suggested will be a problem as once the person has been assigned it would be undesirable if then entering his index number elsewhere would make him appear there but then be unavailable for his previous post! If someone were to make a typo here it could cause trouble.
I appreciate your work on this but is there some other way? i.e if his index number is assigned to lets say F5 then anywhere else his index number appears should indicate "Already Assigned" unless his name appears in $H$29:$K$39 where the cell should then indicate "Not Available".

Regards,
Simon

[Bob Phillips]

Simon,

They would show as 'Alreadt Assigned', so it would then be incumbent on your user to correct it. Correct one, and the name will appear in the other.