Need help with formula

[DKRbella0814]

Can someone help me edit the formula so that it works properly? I'm sure it's a matter of me having parenthesis where they don't need to be or not having them where they are needed.

I basically want the formula to do a series of lookups on a spreadsheet to see if a calculation exists for job#6 (last entered job), if it is "", then lookup the data for the same date in job #5 (2nd last entered job) and return the value unless it is "", then look up the data for the same date for job #4 (3rd last entered job) and return the value unless it is "",...all the way until job #1 (which is the farthest left value on the spreadsheet)

The reason for going from right to left is due to the fact that the farthest right calculations are based on the previously entered data for the jobs before it...and there will not always be 6 jobs entered each day.


Thanks in advance for the help!


=if(vlookup(I6,Calculations2!$A2:$CP2,91,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,91,false),(if(vlookup(i6,Calculations2!$A2:$CP2,76,false)<>"",vlookup(i6,Calculations2!$A2:$CP2,76,false),(if(vlookup(i6,Calculations2!$A2:$CP2,61,false)<>"",(vlookup(i6,Calculations2!$A2:$CP2,61,false),(if(vlookup(I6,Calculations2!$A2:$CP2,46,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,46,false),(if(vlookup(I6,Calculations2!$A2:$CP2,31,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,31,false),(if(vlookup(I6,Calculations2!$A2:$CP2,16,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,16,false),""))

 

[capparfrank]

try this?...

=if(vlookup(I6,Calculations2!$A2:$CP2,91,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,91,false)
,(if(vlookup(i6,Calculations2!$A2:$CP2,76,false)<>"",vlookup(i6,Calculations2!$A2:$CP2,76,false)
,(if(vlookup(i6,Calculations2!$A2:$CP2,61,false)<>"",vlookup(i6,Calculations2!$A2:$CP2,61,false)
,(if(vlookup(I6,Calculations2!$A2:$CP2,46,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,46,false)
,(if(vlookup(I6,Calculations2!$A2:$CP2,31,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,31,false)
,(if(vlookup(I6,Calculations2!$A2:$CP2,16,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,16,false),"")))))))))))

when troubleshooting parenthesis, it can really help to pull your formula out onto a text editor and then start lining everything up. That will help you spot inconsistent parts of highly repetative formulas like this one. Also, i've been known to sit there and count on my fingers . Read left to right, one finger up on every open and one finger down on every close. seriously, you look like a dork but it gets the job done. If you don't tie out at the end of your statements, you can at least start to hone in on the problem.

 

[jasonb75]

You really don't need all those vlookups for what you're trying to do, try

=if(Calculations2!$A2=I6,index(Calculations2!$A2:$CP2,,match(0,COUNTBLANK(OFFSET(Calculations2!$A2,,{90,75,60,45,30,15})))),"")

no guarantee that will work, it hasn't been tested.

If it doesn't work there should be several more efficient ways than your existing method.

As you only have 1 row in the lookup with no error handle, I would assume that it will always be a match, so you could drop my suggestion above to =index(Calculations2!$A2:$CP2,,match(0,COUNTBLANK(OFFSET(Calculations2!$A2,,{90,75,60,45,30,15}))))

However this would need an error handle to return "" if all are blank.

edit: just noticed an error in both formula, will repost with corrected version shortly.

 

[jasonb75]

Tested, and working

excel 07 or newer

=IFERROR(INDEX(Calculations2!$B2:$CP2,,MATCH(0,COUNTBLANK(OFFSET(Calculations2!$A2,,{15,30,45,60,75,90})))*15),"")

older versions

=IF(Calculations2!$P2="","",INDEX(Calculations2!$B2:$CP2,,MATCH(0,COUNTBLANK(OFFSET(Calculations2!$A2,,{15,30,45,60,75,90})))*15))

 

[Aladin Akyurek]

Can someone help me edit the formula so that it works properly? I'm sure it's a matter of me having parenthesis where they don't need to be or not having them where they are needed.

I basically want the formula to do a series of lookups on a spreadsheet to see if a calculation exists for job#6 (last entered job), if it is "", then lookup the data for the same date in job #5 (2nd last entered job) and return the value unless it is "", then look up the data for the same date for job #4 (3rd last entered job) and return the value unless it is "",...all the way until job #1 (which is the farthest left value on the spreadsheet)

The reason for going from right to left is due to the fact that the farthest right calculations are based on the previously entered data for the jobs before it...and there will not always be 6 jobs entered each day.


Thanks in advance for the help!


=if(vlookup(I6,Calculations2!$A2:$CP2,91,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,91,false),(if(vlookup(i6,Calculations2!$A2:$CP2,76,false)<>"",vlookup(i6,Calculations2!$A2:$CP2,76,false),(if(vlookup(i6,Calculations2!$A2:$CP2,61,false)<>"",(vlookup(i6,Calculations2!$A2:$CP2,61,false),(if(vlookup(I6,Calculations2!$A2:$CP2,46,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,46,false),(if(vlookup(I6,Calculations2!$A2:$CP2,31,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,31,false),(if(vlookup(I6,Calculations2!$A2:$CP2,16,false)<>"",vlookup(I6,Calculations2!$A2:$CP2,16,false),""))

Does one of the following return reliably the desired result?...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,
    IF(MOD(COLUMN(Calculations2!$A2:$CP2)-COLUMN(Calculations2!$A2),15)=0,
    IF(ISNUMBER(Calculations2!$A2:$CP2),Calculations2!$A2:$CP2)))

Control+shift+enter, not just enter:

=LOOKUP(REPT("z",255),
   IF(MOD(COLUMN(Calculations2!$A2:$CP2)-COLUMN(Calculations2!$A2),15)=0,
   IF(1-(Calculations2!$A2:$CP2=""),Calculations2!$A2:$CP2)))

 

[T. Valko]

1-(Calculations2!$A2:$CP2="")

Why not simply:

Calculations2!$A2:$CP2<>""

 

[T. Valko]

Originally Posted by Aladin Akyurek
1-(Calculations2!$A2:$CP2="")

Why not simply:

Calculations2!$A2:$CP2<>""

No comment?

I'd like to know the reasoning behind it.