Excel Questions

[RMDrive]

I tried it - it might be the fact that I'm using Excel-2003, but if i set up a vlookup function to select one of those counties from a dropdown list (as you listed, not alphabetically), and I select each one of the counties manually, the function only supplies the right answer twice.  Maybe we're using it in different ways.

Sounds like it. I'm not using drop down lists so maybe that has an impact. I'm using 2003 as well though.

[Hardy]

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

So if you put a 1 in range_lookup (the last number between the brackets) you do need to sort your table in ascending order of the first column. If you put a zero, you don't have to sort, but the penalty is that it returns "N/A" if it doesn't get an exact match. Making range_lookup TRUE (i.e. = 1) always returns a value - it's the nearest approximation to the input value if the table is sorted in ascending order.

[Billys Boots]

So we're both one-third right - excellent solution. 

[Smokin Joe]

If you don't have the counties in alphabetical order, you won't get the right colour when you apply the vlookup function.  Or if you do, it's a fluke.  Try it.

Nonsense.

Just be sure to use "False" as your last argument.  If there is an exact match then it will return the corresponding value, regardless of the order of the data.

[Smokin Joe]

Oops, guess I should have read the rest of the thread 

[RMDrive]

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

So if you put a 1 in range_lookup (the last number between the brackets) you do need to sort your table in ascending order of the first column. If you put a zero, you don't have to sort, but the penalty is that it returns "N/A" if it doesn't get an exact match. Making range_lookup TRUE (i.e. = 1) always returns a value - it's the nearest approximation to the input value if the table is sorted in ascending order.

Sín é. I always use a zero (always trying to find an exact match) so never had to deal with the sorting issue. Good to know though.

[ziggysego]

Reading through some of the stuff here, it's amazing how slack I've gotten. I used to know all this stuff and now I reckon I'd suffer trying to do it again.

Time to re-familiarise myself with Excel and Access again.

[Treasurer]

Reading through some of the stuff here, it's amazing how slack I've gotten. I used to know all this stuff and now I reckon I'd suffer trying to do it again.

Time to re-familiarise myself with Excel and Access again.

I'm an access-nerd, I love it.  Although I don't get to use it as much as I used to in my old job

[Square Ball]

Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error  . I up graded to 2007 a while ago and a few things have changed

[Treasurer]

Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error  . I up graded to 2007 a while ago and a few things have changed

Use countif, ie countif(range,criteria), put the criteria in inverted commas if it's text you're looking at, eg

=COUNTIF(A1:A6,"dog")

[Billys Boots]

Right, in a list I want it to count y, I have tried to do count (y) and a few others but it wont work, I blame user error  . I up graded to 2007 a while ago and a few things have changed

Use countif, ie countif(range,criteria), put the criteria in inverted commas if it's text you're looking at, eg

=COUNTIF(A1:A6,"dog")

So it's =countif(A1:A25,"y") or =countif(A1:A25,"*y*").  That'll count the number of cells with a y in it.  It won't count the total number of ys in the range though (if there are more than two in any cell).

[Mario]

Why would you have 2 y's in a cell? Explain what way the data is set out.

[Billys Boots]

I dunno Mario, I'm just allowing that the 'data' might be text.

[Mario]

I dunno Mario, I'm just allowing that the 'data' might be text.

Sorry Billy Boots, got confused there, thought you where the person with the problem and not the person with the solution.

[ziggysego]

Reading through some of the stuff here, it's amazing how slack I've gotten. I used to know all this stuff and now I reckon I'd suffer trying to do it again.

Time to re-familiarise myself with Excel and Access again.

I'm an access-nerd, I love it.  Although I don't get to use it as much as I used to in my old job

Indeed I remember you helping me with an Access problem I was baffled with and being blown away by the extend of your knowledge on the subject.