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]

you get better help on your question if you add a small set of data, without confidential information on the forum.

 

[BigD63]

Sorry about that, yeah.... I am actually reaching out in a few places, and I thought I added it here, it was elsewhere.

This is A1:I9 C is other data that is not being pulled from anywhere, manually entered, just disregard C

[/URL][/IMG]

Basically I am working on D2 currently, as once I figure that out, im sure the rest will not be hard. All other data is currently on the same sheet, just incase the sheets were the issues before.

A2 and B2 are validation which I have working, and they are pulled from AD3 through AG10. Source for the data validation is below.

[/URL][/IMG]

That is the validation area, I have checked, doublechecked, rechecked and cheked again for trailing spaces, and I have considered just making these cell an = to the cells below to make sure there are no errors, because other forums seem to think this is the ONLY reason it is not working. But the validation parts work like a charm.

This is the data, this is obviously BS, but I made it to the exact location and cell count as the real doc.

[/URL][/IMG]

So the goal is to have the second validation grab the city, Anaheim and it will populate the draft beers sold in Anaheim, then I will make additional formulae in E through I to reference D through H in the pic above, just populating the data straight across.

Honestly I am happy to share this file with you here, in PM or by email if it will help. So far the formula I have had the best luck with is this: =VLOOKUP(B2,CHOOSE({1,2},B61:B79,C61:C79),3,0)

Thanks alot.

 

[Oeldere]

Then post an excel file on the forum.

After that I will take a look at it.

 

[BigD63]

I tried to attach it directly, but I wasn't sure how, that's why I screen shotted everything. I just linked it to dropbox.

Tonight I made a bit more progress, and I am honestly not sure if it was just some bad data, because I copied the formula into notepad, deleted the cell, and put the formula back and it started working-ish. The validation works fine, but on C2 in COMPARISON FINAL there is validation I have never setup, it just appeared there, but the formulas will grab SOME right info and other times no clue where it is getting the info from, complete loss.
in the DATA tab, I added a blank column a one point to match exactly the final sheet, I can remove that. Also, I can move C column on Comparison FINAL if that is messing things up.
The first comparison tab was for someone else who was offering to help, who REALLY cut up the sheet, scattered the data and made validations that would only work once, then said he was confused, so I color-coded the heck out of it to get it where it is now, but that sheet can be COMPLETELY ignored at this point.
Also, there is an exact duplicate of the main info section in DATA around row 60 on COMPARISON FINAL, as that was from testing having it on the same sheet. We can use that as well, not really particular where these need to go, as long as my colored cells work as intended, and it is something easily repeatable for the sections below for comparisons. Also I do need to keep the 4 digit decimal minimum, and IO prefer the formatting that way.

So without further overtalking, you have a dropbox link below.

Dropbox - BSdemosheet.xls

Again, thank you very much!

 

[Oeldere]

I will help you if you add the file on a forum (place) where I can download it without registration.

Instead of a long story, just add which cells (on which) sheets need the formula.

just add where the source of the data is.

 

[BigD63]

I didn't see a means of attaching it here. Any place in specific you prefer?

The Excelhelp forum will allow an easy download, but I do not know if you have an account there. I am looking for a site that hosts like that currently.

 

[BigD63]

You should be able to DL form here without an account.

BSdemosheet.xls - Google Drive

The lookups in D2 through I2 are the problem.
I want them to look up the reference in Comparison Final B2 and pull up the referenced items either from directly below or form the DATA sheet, does not matter, info is IDENTICAL.
The 4 digit decimal place does need to be kept, because the real info it does matter.
So the selection in B2 populates D2 through I2.

Again, thank you for the assistance.