Populate data from drop-down list selection

Hello - I've been reading through a few posts to try and figure out how to solve my problem, but can't seem to get it right I've tried a combination of formulas using vlookup's, index and match to no avail...

Anyway, I've attached a sample file. Essentially, I want to select a region and month on the 'Validation' tab from the two independent data validation lists and have the data (revenue) populate in cells C7-G7 for Products A - E for the respective region and time period. (The source tab is titled 'Data')

Please let me know if I need to clarify anything - but I think it'll be pretty straight forward once you view the file. Really appreciate any and all help on this, thanks in advance!

HI

please re do your data sheet as told by "FDibbins", i have pasted those values and provided a solution, i am not sure whether it as per u r requirement or not.


Punnam

Thank you all for replying!

@Punnam - Yes, that's exactly how I want it to appear when a region and month is selected - so the values change and correspond to the selected region and month.

@Miroslav - The formula is similar to one's I've seen in this scenario, however, I get an error message when testing. It highlights on D22 after the index function - any thoughts why? I followed your instructions and entered in cell C7


Thanks again!
MC

Hi MC,

Was u r question answered Solved ?

Punnam

Hi Punnam - No my question hasn't been solved yet ... I was confused on what FDibbins suggested to do with re-working the Data set; not sure exactly how to fix.

Hi MC,

So u are trying to fetch data which is under circular reference ?

Punnam

what exactly are you trying to do in Data sheet?

@ Punnam - yes i'm trying to include the data under circular reference, but I'm trying to reformat to not have a circ ref error. I'm not really concerned with how the data sheet appears as it will be hidden, my struggle is setting up a working formula that calls in the data via the drop down selections. Should I recreate the data set? Thanks again!

HI MC,

Yea you need to make it free from circular references as told by FDibbind, they will throw errors very often

Punnam

Hi @ FDibbins, with the Data Sheet, I'm simply trying to group revenue by Products A-E by month. It doesn't have to stay in this exact format - I laid it out as such as I thought it would be simple to retrieve via a vlookup or index function. How should I rearrange the data file to avoid circ ref errors? Sorry if I'm not communicating my problem properly... I appreciate your help and patience!

I pull it in from a report builder client in excel. The data is confidential, so I kept it generic

Hi MC,

To remove circular references using Error Checking-Circular reference.

Punnam

OOOOK, I see the problem now!! I looked at the original file I uploaded and saw the formulas you are referring to; those are a copy/paste error - that's why I was so confused with your original post, FDibbins. All cells on the data file should just be raw data, no formulas. I'm reattaching the file with the formulas removed. Does that help?

Hi MC,

Is anything else is i have forgot to link up guide me.

Punnam

Thanks, Punnam - you have it linked properly , now is there a way to link to the drop down lists?

Hi Mc,

So you don't want to have data validation?
am i correct ?

Punnam

I want the data to populate in the appropriate cells based on the drop down list selection, so if I select France amd the month of April from the lists, I want the corresponding values in the Data tab to populate in the cells for all products, make sense?

Plz check the cells which are colored with Yellow, it is having a drop down list as u requested

Brilliant! Exactly what I need. Thanks so much

Hi MC,

1) Can you let us know the post 25 is referring to whom?
2) was your question answered and solved ?

Punnam

Well thanks everyone for chiming in to help, but #25 was directed at you, Punnam. Yes, question was answered and solved.

Hi MC,

welcome, so take some time and mark the thread solved in tools and rate any one whom you feel have helped you in solving .

Punnam