If "value" in a certain range

[fastcar]

Hi there,

I have a table which has start times in col "B" and finish times in col "C" and in col "H" I would enter either "d,w,m,r", starting from row 26 to 54. It looks like this roughly...

21:33 21:36 w
21:36 23:00 d
23:00 23:45 w
23:45 00:30 m

and so on......

Now what I would like to do is if there is an "M" in col "H" to copy the contents of col "B" and "C" in that row to "G15" and "H15" respectivley so using my example above because there is an m in the bottom row "G15" would have "23:45" in it and "H15" would have "00:30" in it.

I would rather do it with a formula if possible.

I hope I have explained this clearly if not please let me know what other information you need.

Thanks

[teylyn]

Hello,

your question is not clear. Please post a sample Excel file to illustrate your data layout. Specifically, if column H contains manually entered text, then you cannot have a formula in H15 as well.

If H15 is above the data table, and you want to process the data below, then it is not clear how multiple occurrences of the value "m" in column H should be handled.

Please post an Excel file and mock up the expected result manually to demonstrate what you want too achieve.

cheers,

[TMS]

If you've got an "m" in column H, how do you plan to get the contents of column C into it?

You could have, in cell G15: =IF(H15="m",B15,"")

Regards, TMS

[fastcar]

Ok I have edited my first post while I make something up to show what I mean better

Thanks

[teylyn]

fastcar, please do NOT change a post after it has received replies. Reading the thread will not make sense to anybody if you change the text that people have responded to. I have restored your original text in question.

Use a comment to post your corrections.

[fastcar]

fastcar, please do NOT change a post after it has received replies. Reading the thread will not make sense to anybody if you change the text that people have responded to. Use a comment to post your corrections.

My apologies I was just trying to be helpful.

Here is an excel sheet to show you better what I am trying to do.EXAMPLE.xlsm

[teylyn]

What do you want to see in row 15 if there is more than one row with an "m" in column H?

[teylyn]

In G15
=INDEX($B$26:$B$54,MATCH("m",$H$26:$H$54,0))

in H15
=INDEX($C$26:$C$54,MATCH("m",$H$26:$H$54,0))

This will only find the first occurrence of "m" in column H, though. If there are more, they will be ignored.

[fastcar]

In G15
=INDEX($B$26:$B$54,MATCH("m",$H$26:$H$54,0))

in H15
=INDEX($C$26:$C$54,MATCH("m",$H$26:$H$54,0))

This will only find the first occurrence of "m" in column H, though. If there are more, they will be ignored.

Work fine, just one small addition I have thought of if possible. If there is no "m" would it be possible to have "NO" in G15 and "BREAK" in H15 if not then its no big deal.

Re: If "value" in a certain range
What do you want to see in row 15 if there is more than one row with an "m" in column H?

There should only be one "m" in that column.

Thanks for the great and very fast reply

[teylyn]

Depending on your Excel version (please update your profile)

Excel 2003 and earlier

=if(iserror(MATCH("m",$H$26:$H$54,0)),"no",INDEX($B$26:$B$54,MATCH("m",$H$26:$H$54,0)))


Excel 2007 and later

=iferror(INDEX($B$26:$B$54,MATCH("m",$H$26:$H$54,0)),"no")