I have a complicated array situation

[JoshuaFreeman]

I have a table where the text "pit1" appears in multiple different rows and columns (ex: "pit1" is in c7, j6, and p16). In the 3 columns next to each of these, there are time values. Ex: c7=pit1, d7= 03:15, e7=05:45, f7=8:15. I'm trying to write a formula that will search the entire spread sheet for "pit1" and then test if a time value appears in one of the 3 next columns and return some misc. value if true, and leave blank if false. Basically, something to the effect of: if "pit1" exist and has a value of time xx:xx then display misc info, else blank.

 

[pgc01]

Hi
Welcome to the board

I don't undertand what this means:

Basically, something to the effect of: if "pit1" exist and has a value of time xx:xx then display misc info, else blank.

What is misc info?

For the example you posted what would be the result?

 

[JoshuaFreeman]

The misc info would be a value of 1 or anything needed to satisfy conditional formatting.

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 268"]
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;" span="2"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;" span="5"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <tbody>[TR]
[TD="width: 32, bgcolor: #92D050"] [/TD]
[TD="width: 32, bgcolor: #92D050"] [/TD]
[TD="width: 34, bgcolor: #92D050"]pit1[/TD]
[TD="width: 34, bgcolor: #92D050, align: right"]6:00[/TD]
[TD="width: 34, bgcolor: #92D050, align: right"]22:00[/TD]
[TD="width: 34, bgcolor: #92D050, align: right"]18:30[/TD]
[TD="width: 34, bgcolor: #92D050"] [/TD]
[TD="width: 17, bgcolor: transparent"] [/TD]
[TD="width: 32, bgcolor: transparent"] [/TD]
[TD="width: 34, bgcolor: transparent"] [/TD]
[TD="width: 36, bgcolor: transparent"]Result[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]pit2[/TD]
[TD="bgcolor: #92D050, align: right"]7:00[/TD]
[TD="bgcolor: #92D050, align: right"]12:00[/TD]
[TD="bgcolor: #92D050, align: right"]18:00[/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]pit1[/TD]
[TD="bgcolor: transparent, align: right"]6:00[/TD]
[TD="bgcolor: yellow"]found[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"]pit3[/TD]
[TD="bgcolor: #92D050, align: right"]6:20[/TD]
[TD="bgcolor: #92D050, align: right"]12:20[/TD]
[TD="bgcolor: #92D050, align: right"]18:20[/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]pit2[/TD]
[TD="bgcolor: transparent, align: right"]22:00[/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"]pit4[/TD]
[TD="bgcolor: #92D050, align: right"]6:02[/TD]
[TD="bgcolor: #92D050, align: right"]12:01[/TD]
[TD="bgcolor: #92D050, align: right"]23:00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]pit3[/TD]
[TD="bgcolor: transparent, align: right"]6:20[/TD]
[TD="bgcolor: yellow"]found[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]pit4[/TD]
[TD="bgcolor: transparent, align: right"]18:20[/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]*[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]*[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: #92D050"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]*[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[/TR]
</tbody>[/TABLE]


Name (in green)

Data - Refers To: Sheet1!$A$1:$G$8


Formula

In K2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(SUM(COUNTIF(OFFSET(Data,
IF(Data=$I2,ROW(Data)-ROW(INDEX(Data,1,1))),
IF(Data=$I2,COLUMN(Data)-COLUMN(INDEX(Data,1,1))+1),1,3),$J2)),"found","")

Markmzz

 

[JoshuaFreeman]

That might actually work. I'll let you know how it goes.

 

[JoshuaFreeman]

OMG, THANK YOU Markmzz!!!!!!!!!!!!! You're awesome. I'll have to study the formula to see how you did it. (I haven't fooled around with excel in 10 years and I stupidly told my boss that I was good with it). Thanks for saving my rear!

 

[markmzz]

OMG, THANK YOU Markmzz!!!!!!!!!!!!! You're awesome. I'll have to study the formula to see how you did it. (I haven't fooled around with excel in 10 years and I stupidly told my boss that I was good with it). Thanks for saving my rear!

You are welcome and thanks for the feedback.

Markmzz