Validation and Index/Match

[BigD63]

Hello,
first this is an old version of Excel, it is 2003 SP3 but, I have never needed more than it can do, so I never upgraded it, so please keep that in mind, as I know options change from revision to revision

I have a chart I am working on with one sheet called data and it is just a big grid of info, 2 columns of headings, and everything else is numbers.

Problem 1: Validation.
In my report section, I have in A2 a validation for states, it works, there is a pull down that has info for 5 states.
Now I can make a BIG validation pulldown list in B2 for all the cities listed, but I want it to show only the cities in the specific state, so you cannot pick Oregon Los Angeles. The data is separated and organized in the data section, so A2 is a state, and B2:B6 are cities in that state, than A7 is a different state, and B7:B11 are different cities in THAT state.

Problem 2: Pulling other data over.
I think it will be easiest to make one formula, then copy it into a few cells and adjust 2 cell references. This formula sits initially at COMPARISON!D2. But for the moment, I am trying to write a Index/Match to say look at B2 in the COMPARISON sheet (basically the report sheet) match that to the city name in DATA!B2:B44, once the match is found (say it matches in DATA!B15) provide the data in the in the next cell C15. Here is the formula I have, and it does not work, I know it is not right but I am not sure what I missed. =INDEX(DATA!B2:B44, MATCH(COMPARISON!B2,DATA!C2:C44,0))

The goal is, first pulldown menu I get a state (done), second pulldown menu I get cities, but only cities associated with that state, then D2 then references the data adjacent to the city reference in the DATA sheet.

Once this is done I will duplicate the formula from D2 into E2:I2 and just change a couple cell references to pull the correct data, as each city has 6 adjacent cells of info.

I know this is gonna be easy for the gurus, but I know I am missing something and I am not sure what. Can you help?

 

[Oeldere]

=INDEX(DATA!B2:B44, MATCH(COMPARISON!B2,DATA!C2:C44,0))

You need to add the column in that formula e.g. column 4, see the red text.

=INDEX(DATA!B2:B44, MATCH(COMPARISON!B2,DATA!C2:C44,0),4)

 

[Oeldere]

Create multiple linked dropdown list in excel
How to Create Multiple Linked Dropdown Lists in Excel
Excel: Dynamische afhankelijke datavalidatie | Finch's weblog
403 Forbidden
VBA voor smarties: Afhankelijke validatie also in Englisch

 

[BigD63]

OK, now I understand why my validation wasn't working, that is all fixed now. Thank you for that!

The fix for the formula you gave me did not fix it though. =INDEX(DATA!B2:B44, MATCH(COMPARISON!B2,DATA!C2:C44,0),4) Question, what does the ,0 actually mean? I have seen several formulae that have that and I am not sure what it is referencing.

When I walk through the formula fix in Excel, it shows it is looking for the item in the second validation box, but always brings back a N/A for the result, so I am somewhat lost.

I am sure this is a VERY basic function, but I haven't done any real function stuff in excel in years, so I know I am missing stuff.

 

[BigD63]

Actually is it the ,0 portion that is bringing back the N/A.

 

[Oeldere]

0 = exact match
1 = NOT exact match

Did you work with datavalidation? => Data => Datavalidation

It seems your input is not exact the same as the comparison sheet. You have trailing or leading spaces in the name?

If the result is not found you get the (failure)result N/A (Not Available).

You could test the length of the input and comparison cell with the formula LEN().

 

[BigD63]

Yes, I got the Data Validation working great now, so that is fine.
I did check the character length as well, it is all the same.

Could cell formatting be causing this? They are all stored as "General" could that be a problem, as the numeric values that I am trying to pull over are formatted as numbers with 4 decimal places.

I cannot find any way these are different to cause the block in the formula. All values in the validation have been verified, copy/pasted in and out of notepad to clear formatting, including leading/trailing spaces, I cannot find any way these are different, but theformula will not work.

I have tried trimming down the data, incase there were some gaps causing the issue, tried even coping it onto the same sheet, just WAY below, nope, also tried VLOOKUP, i am **NOT** married to the INDEX/MATCH idea, I can use VLOOKUP or anything else that will work.

I qam just trying to avoid writing a HUGE If/ELSEIF/etc string to do this.

Below are the current versions I have tried:

=VLOOKUP(B2,DATA!A2:H20,3)

=INDEX(DATA!B2:B44, MATCH(COMPARISON!B2,DATA!C2:C44,1),4)

=INDEX(DATA!$B$2:$B$44, MATCH(COMPARISON!B2,DATA!C2:C44,1),4)

=INDEX(DATA!$B$2:$B$44,MATCH(COMPARISON!B2,DATA!C2:C44,1),4)

=VLOOKUP(B2,B60:B79,3)
This one above is when I tried copying the DATA into the comparisons sheet.

I have seen several tutorials that insist on the $ in the cell references, but I am not sure why.

I can do the length test, what would be the exact formula for that, as I am unfamiliar with it.

 

[BigD63]

I just tried this, and it got closer than the others I have tried, this still has the data below it on the same page.

=VLOOKUP(B2,CHOOSE({1,2},C61:C79,B61:B79),3,0)

When I chose to evaluate the error, it showed in the window ALL my data being pulled,. it just had the error a ,3,1)

 

[Oeldere]

=VLOOKUP(B2,DATA!A2:H20,3)

Although you can leave the last criteria, I advice you to add it in the formula.


=VLOOKUP($B2,DATA!$A$2:$H$20,3,0)

Did it work for the first value?

Maybe the problem is that you don't add $ signs in your formula.

In that case you also can try the formula below.

=INDEX(DATA!$B$2:$B$44,MATCH(COMPARISON!B2,DATA!$C$2:$C$44,1),4)

$A keeps the A if you copy to the right.

$1 keeps row 1 if you copy to below

$A$1 keeps always the cell A1 when you copy it.

 

[BigD63]

I did try both, and neither of them worked, still kicked back N/A in the cell. I have tried a variety of formula types at this point, Index/Match, VLookup, VLOOKUP/CHOOSE, the CHOOSE has gotten me closest so far.
I have migrated the data onto one sheet, as I have seen different sheet references hose things up before, so that's why the cell references are VERY different.

The formula below, I walk through it in the error check in excel and it brings up all the data, but then it always kicks back an N/A at the ,3,0) part. And the cell says #REF !

Any thoughts?

Here is the formula that has come closest: =VLOOKUP(B2,CHOOSE({1,2},B61:B79,C61:C79),3,0)

BTW I really do appreciate the time you are taking on an excel flunky