Formula Modification

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The formula below work fine. However, it's come to my attention that I need to trap or look for certain codes the Events sheet column H10:H311 which are as follows: J, K, M, N, P, Q, R, S, T, U.

=IF(B11<>"",SUMPRODUCT((Events!$A$10:$A$311=B11)*(Events!$B$10:$B$311+Events!$C$10:$C$311>='General log'!Y11 + 'General log'!Z11),Events!$N$10:$N$311),""

Then grab the related value for that record as seen above. So, within the second section of the formula : *(Events!$B$10:$B$311+Events!$C$10:$C$311>='General log'!Y11 + 'General log'!Z11),Events!$N$10:$N$311),"") I need to place a reference to column H10:H311 to grab the code value of if it's there for (J, K, M, N, P, Q, R, S, T, U), then, the whatever value is found in column Events!$N$10:$N$311 for that record

Is this possible? Can someone assist. I would apprciate any help on this

Thanks,...
 
I am not sure, but couldn't you do the "grabbing" by embedding a vlookup into the "if" statement? In other words, first test for the desired non-blank condition, then if true, add a vlookup where you specify the range. Of course first you have to highlight the range area for the lookup, and name it in the upper left box (right below where it says File Edit). It would be like this: if(condition,vlookup(lookup critria cell,rangename,column count--1 for first 2 for second etc--within range name for data,false)

Hope this helps.
 
Peter

=IF(B11<>"",SUMPRODUCT((Events!$A$10:$A$311=B11)*(Events!$B$10:$B$311+Events!$C$10:$C$311>='General log'!Y11 + 'General log'!Z11),Events!$N$10:$N$311),""

You see, the formula used in the above example works. All I need to do now, is add the column Events!$H$10:$H$311 to the mix. Any row can contain either on of those codes mentioned earlier. I need the code to be pretty close to what it is now, only now pulling any value it sees that matches or is related to each record if that code is there. It's kind of like a VLOOKUP as you say, only intead of looking for one value say: "K", it looks for multiple code values. Oh, I created a Named range containing the codes named: "DownTimeCodes

I would appreciate any assistance with this

I have a bit of a hard time envisioning what you are saying, can you use the function above and modify it using the named range : "DownTimeCodes"

----- peter wrote: ----

I am not sure, but couldn't you do the "grabbing" by embedding a vlookup into the "if" statement? In other words, first test for the desired non-blank condition, then if true, add a vlookup where you specify the range. Of course first you have to highlight the range area for the lookup, and name it in the upper left box (right below where it says File Edit). It would be like this: if(condition,vlookup(lookup critria cell,rangename,column count--1 for first 2 for second etc--within range name for data,false)

Hope this helps.
 
=IF(B11<>"",SUMPRODUCT(--(Events!$A$10:$A$311=B11),--(Events!$B$10:$B$311+Ev
ents!$C$10:$C$311>='General log'!Y11 + 'General
log'!Z11),--ISNUMBER(MATCH(Events!$H$10:$H$311,DownTimeCodes,0)),Events!$N$1
0:$N$311),"")

I assumed that K, etc are down times codes and make up the range
DownTimeCodes.

Syd said:
Peter:
Click to expand...
=IF(B11<>"",SUMPRODUCT((Events!$A$10:$A$311=B11)*(Events!$B$10:$B$311+Events
!$C$10:$C$311>='General log'!Y11 + 'General
log'!Z11),Events!$N$10:$N$311),"")
You see, the formula used in the above example works. All I need to do
Click to expand...
now, is add the column Events!$H$10:$H$311 to the mix. Any row can contain
either on of those codes mentioned earlier. I need the code to be pretty
close to what it is now, only now pulling any value it sees that matches or
is related to each record if that code is there. It's kind of like a VLOOKUP
as you say, only intead of looking for one value say: "K", it looks for
multiple code values. Oh, I created a Named range containing the codes
named: "DownTimeCodes"
I would appreciate any assistance with this.

I have a bit of a hard time envisioning what you are saying, can you use
Click to expand...
the function above and modify it using the named range : "DownTimeCodes"
----- peter wrote: -----

I am not sure, but couldn't you do the "grabbing" by embedding a
Click to expand...
vlookup into the "if" statement? In other words, first test for the desired
non-blank condition, then if true, add a vlookup where you specify the
range. Of course first you have to highlight the range area for the lookup,
and name it in the upper left box (right below where it says File Edit). It
would be like this: if(condition,vlookup(lookup critria
cell,rangename,column count--1 for first 2 for second etc--within range name
for data,false).
 
Back
Top