Reference to cells

[Ismael]

Hi to all in the forum.

My doubt is about references to cell in order to make drop down the cells.

As you guys can see in the file attach, in column F I have values of time, then in column D and E I have to time, the column D have the start time and the the column E have the end time.

The objective of the formula that I made in F8 is return the value in D4 if the value of F8 is "inside" the start time and end time of D4 and E4, the problem is that after I fill the cell G8:G11 I want to check what are the limits in the cells D5 and E5 and do the same thing.
But how can I do this, because if I don't make D$4 and E$4 wen a make the drop dow this values change.

I don't now if I explain well what I pretend, so if you guys have any doubts, please tell me.

Best Regards,

Ismael

[Bob Phillips]

Guessing somewhat


=INDEX($B$4:$B$7,MATCH(SUMPRODUCT(--($D$4:$D$7<=F8),--($E$4:$E$7>=F8),$D$4:$D$7),$D$4:$D$7,0))

[Ismael]

Hi XLD,

Thanks for the reply. I will test your formula and then I will post back.

Best Regards,

Ismael

[mdmackillop]

Hi Ismael,
XLD's solution is fine if all your data fall between listed periods. It will return a false result if this is not the case (e.g. 06:30)
As I'm no good at these complicated formulae, here is a VBA solution using a Used Defined Formula (UDF). See the attached example.


Option Base 1

Function Between(Tgt As Range, Data As Range)
Dim MyData()
rw = Data.Rows.Count
col = Data.Columns.Count
ReDim MyData(rw, col)
MyData = Data.Value
For i = 1 To rw
If Tgt >= MyData(i, 3) And Tgt <= MyData(i, 4) Then
Between = MyData(i, 1)
Exit Function
End If
Next
End Function

[Bob Phillips]

XLD's solution is fine if all your data fall between listed periods. It will return a false result if this is not the case (e.g. 06:30)

Good point, I didn't notice that.

An alternative


=IF(SUMPRODUCT(--($D$4:$D$7<=F8),--($E$4:$E$7>=F8))=0,"",
INDEX($B$4:$B$7,MATCH(SUMPRODUCT(--($D$4:$D$7<=F8),--($E$4:$E$7>=F8),$D$4:$D$7),$D$4:$D$7,0)))

[mdmackillop]

That works for me, but I wouldn't like to memorise it!:clap:

[Bob Phillips]

That works for me, but I wouldn't like to memorise it!:clap:

You don't have to. When I was a young maths student, I memorised a few basic rules, and then I learnt to quickly work out the more complex stuff from those axioms. That is all that is needed. http://vbaexpress.com/forum/images/smilies/001.gif

[Ismael]

Hi XLD and MDM,

In first place thanks for the reply's.

Now regarding the solution of MDM, I have 2 question, why the code returns a message of error if we delete the content of the cell in column D and E? Then if we pull down the column I in (File2) and then pull down the column J the message of error appears again, why this happens?

XLD, regarding your solution now, your second formula works ok, the only problem that happens is for example in cell C4 if you type 15000 in the cell G8 the formula returns #N/A, I try to understand why this happens but without success. So if you can give me same tips about the formula I appreciate.

Best regards,

Ismael

[Bob Phillips]

XLD, regarding your solution now, your second formula works ok, the only problem that happens is for example in cell C4 if you type 15000 in the cell G8 the formula returns #N/A, I try to understand why this happens but without success. So if you can give me same tips about the formula I appreciate.

The problem is created because you have two times the same, which surely is invalid data.

[Ismael]

Hi XLD,

Yes in fact just now I see that...
Sorry the trouble.
But can you explain me how the formula works?

Regards,

Ismael

[Bob Phillips]

But can you explain me how the formula works?

I seem to be spending my life explaining how formulae work these days. Time for a bookhttp://vbaexpress.com/forum/images/smilies/001.gif.

It really is very simple


SUMPRODUCT(--($D$4:$D$7<=F8),--($E$4:$E$7>=F8))

simply counts how many time periods that the time in that row occurs in the lookup data. It should only be once on not at all (that is why it failed when you created two similar time periods).

This is tested against 0, setting the answer to blank if so, that is no match.

Otherwise

it just matches the start of that time period in the lookup values to get an index into that table


MATCH(SUMPRODUCT(--($D$4:$D$7<=F8),--($E$4:$E$7>=F8),$D$4:$D$7),$D$4:$D$7,0))

It then passes that index to the INDEX function, looking up the code value


INDEX($B$4:$B$7,idx)

[Ismael]

Hi XLD,

Thanks for the tips, now I understand a little more about your formula.

But tell me one thing, it's possible create boards around the cells that have the same content (example in attach), if it is, this will give me a more comprehensive "draw".

Mabye same code in VBA can make this.

So if you guys can give a help, I really appreciate.

Thanks in advance.

Best regards,

Ismael

[Bob Phillips]

But tell me one thing, it's possible create boards around the cells that have the same content (example in attach), if it is, this will give me a more comprehensive "draw".

Mabye same code in VBA can make this.

Conditional formatting.

[Ismael]

Thank you very much XLD.

Good weekend

regards,

Ismael