Ask Your Question

I need to have a drop down which returns the value of a row [closed]

asked 2016-02-29 14:36:25 +0200

Greyghost60 gravatar image

Hi and help I trying to have a drop down which brings back a row rather than one cell

as in
20 Jan 16 2.00pm INDOOR TEAM Heathfield away WHITE 2R

I can do it by adding all the values into on cell by then the date part becomes a number format not a date and I need to sort the drop down



edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-01 16:23:44.911798

2 Answers

Sort by » oldest newest most voted

answered 2016-03-15 00:23:27 +0200

Greyghost60 gravatar image

updated 2016-03-15 00:25:14 +0200

Hi I found the answer. I needed the data as a reference to a vlookup and simply changed the reference from a 1 or 2 digit number to a 3 eg start at 100 then =(Text(AP2,"ddd dd mmm"))&" "&AQ2&" "&AR2&" "&AS2&" "&AT2&" "&AU2&" "&AV2&" "&AO2

gives in cell B3

Sat 20 Feb 2.30pm FRIENDLY website trial match home WHITE 3 TRIPLES 101 and =VLOOKUP(RIGHT(B3,3),BC2:BE80,2,0) gives the right answer.

i would like to hide the 101 but that's proving difficult

edit flag offensive delete link more

answered 2016-02-29 18:18:41 +0200

Lupp gravatar image

updated 2016-02-29 18:36:54 +0200

As far as I understand your "value of a row" is meant to be some concateantion result starting with a date-time value.
You know that that concatenated date-time value only can be text the actual value was converted into using the TEXT function. (Otherwise, converted automatically, it would look like a decimal number.) Dates, time values, and date-time values like general numerics, too, converted into text can only be sorted correctly if they are converted based on a strict format ensuring
1) the more significant parts nearer to the front
2) same length for all the values one part can have (except possibly trailing decimal places)
3) no exponent parts

Back to Date-Time specifically:
The ISO 8601 standard (which is the only internationally recognised one) is made to exactly ensure the above conditions. You may use it slightly adapted to human preferences reading dates:
=TEXT(Date+Time;"YYYY-MM-DD HH:MM:SS")&Delimiter&TheRestOfTheRow
(You may omit the ":SS" or - to the contrary - append ".00" for two decimals e.g. depending on the accuracy you want.)
am/pm is a deprecated remnant from very old times. An absolute no-go with this respect.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-02-29 14:36:25 +0200

Seen: 74 times

Last updated: Mar 15 '16