Trying to find a easier way to do this nested formula

[mthroesch]

testing.jpg

wanted to know if there is a easier way then to just nest a lot of if statements.

so what im doing is i have 2 columns that will be filled out. Accept, Refuse.

Columns U:V are used for what does the letter mean.
Range(E2:H2) are the number that will be stored in Columns A and B
Range(E4:H9) are all the possibilities that should be possible.

Im trying not to have to make a 30 line nested If statement if possible.

example of what im currently using.
=IF(AND(IFERROR(VLOOKUP(E5,$U$3:$V$6,2,FALSE),"")="Refuse",IFERROR(VLOOKUP(F5,$U$3:$V$6,2,FALSE),"")="Accept"),IF($F$2=MAX($E$2:$H$2),$E$2,MAX($E$2:$H$2)-$F$2),"0") would return 2 in the B5 cell

any help would be great.

[mthroesch]

so i guess what im asking is, is there a way to
for accept column look at Columns E:H find the Accept and put the hrs in the A column
for the refuse column find the refuse if its prior to the Accept put the difference (between the max hrs and the accepted hrs ) in the B column

[hrlngrv]

Would there ever be more than one A in any row of E4:H9? If not, =IFERROR(INDEX($E$2:$H$2,MATCH("A",$E5:$H5,0)),0) for E5:H5.

For E6:H6, there are 2 Rs before A in G6. Do you want the E2 or F2 value?

[mthroesch]

sorry for the confusion. so the reason for Columns U:V is there are a lot of options that narrow down to Accept, Refuse, or blank. the columns E:H and Data validation columns that pull from Column U. i tried to simplify my question by reducing to 3 options.

for ex: E=Doesn't get Asked (Blank), F=doesn't want(Refuse), G=Wants (Accept), H= Don't ask(Blank).

there can be only one accept. but could be multiple refuse and blanks in each row. if first row is Accepted then the rest or blank. ext...

Thank you for the help so far. I apologize again for not explaining better


Edit: also for the refuse as they can refuse multiple times. it will only grab the Max Value. ( I am working on it to prevent a higher number from coming after smaller number)

[mthroesch]

This is what i came up with it seems to work just fells ugly. I really hate nested if statement. i fell i should of just took a shot at this in the VBA. was just trying to stay away from macros and such.

if any one has a better way. plz tell me.


Accept formula
=IF(IFERROR(VLOOKUP(E4,$U$3:$V$12,2,FALSE),"")="Accept",$E$2,IF(IFERROR(VLOOKUP(F4,$U$3:$V$12,2,FALSE),"")="Accept",$F$2,IF(IFERROR(VLOOKUP(G4,$U$3:$V$12,2,FALSE),"")="Accept",$G$2,IF(IFERROR(VLOOKUP(H4,$U$3:$V$12,2,FALSE),"")="Accept",$H$2,"0"))))

Refuse Formula
=IF(IFERROR(VLOOKUP(E4,$U$3:$V$12,2,FALSE),"")="Refuse",MAX($E$2:$H$2)-A4,IF(IFERROR(VLOOKUP(F4,$U$3:$V$12,2,FALSE),"")="Refuse",MAX($E$2:$H$2)-A4,IF(IFERROR(VLOOKUP(G4,$U$3:$V$12,2,FALSE),"")="Refuse",MAX($E$2:$H$2)-A4,IF(IFERROR(VLOOKUP(H4,$U$3:$V$12,2,FALSE),"")="Refuse",MAX($E$2:$H$2)-A4,))))

[mthroesch]

Never mind i was wrong it doesnt work right. the refuse formal doesnt work like i thought

[mthroesch]

This is what I have so far. https://drive.google.com/file/d/18uF...ew?usp=sharing

only thing i cant quite figure out now is getting columns G thru J to work the way I want them to with out excel crashing.
so columns A and B are either Accept or refuse based off of Columns G thru J. Works the best it can.
Columns C thru F are polls as in ascending order bases on whether G thru J is Accept or refuse if either they get counted. works
Columns K thru N are just if G thru J are Accept. skipping any other options. works

so what im after is making G thru H do something. G links to K ect. so if G is accept but there is already max ppl it clears G and msgbox "Already have X Persons Start next Poll"

Ive tried this: but it locks up and crashes Excel.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.