# Populate data from drop-down list selection

1. ## 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!

2. ## Re: Populate data from drop-down list selection

You have some pretty glaring circular references in Data sheet!! (circ ref is when a cell is used within a calc that includes that cell)

for instance in D5, downwards you have...
=SUMIF(\$D\$4:\$D\$365, \$E5,D\$4:D\$365)
meaning...
in D5, put the sum of the range D4:D365 (which includes D5), based on what is in E5. Soooo D5 is part of the sum that goes INTO D5

Column E is even worse...
E5=SUMIF(\$D\$4:\$D\$365, \$E5,E\$4:E\$365)
So E5 has to be the sum of items in D, based on what's IN E5

Fix those 1st, then we can look at the rest

3. ## Re: Populate data from drop-down list selection

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

4. ## Re: Populate data from drop-down list selection

Hi there,

Originally Posted by FDibbins
You have some pretty glaring circular references in Data sheet!! (circ ref is when a cell is used within a calc that includes that cell)
I freeze them all and You have to do the same if You want to get it to work with this formula (put in cell C7 in validation sheet and drag to right):

Formula:
`Please Login or Register  to view this content.`

Note that this is array formula and needs to be confirmed by Ctrl+Shift+Enter. Also it is designed to fixed range in Your Data sheet.

Hope it helps

5. ## Re: Populate data from drop-down list selection

@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

6. ## Re: Populate data from drop-down list selection

Hi MC,

Was u r question answered Solved ?

Punnam

7. ## Re: Populate data from drop-down list selection

Originally Posted by Miroslav R.
Hi there,
I freeze them all and You have to do the same if You want to get it to work with this formula (put in cell C7 in validation sheet and drag to right):
I know how to use circ errors, but they are never a good idea to leave them in a workbbok - they often cause all sorts of wierd things to happen, and are only used for very specific situations...I do not believe this is 1 of them

8. ## Re: Populate data from drop-down list selection

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.

9. ## Re: Populate data from drop-down list selection

Hi MC,

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

Punnam

10. ## Re: Populate data from drop-down list selection

what exactly are you trying to do in Data sheet?

11. ## Re: Populate data from drop-down list selection

@ 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!

12. ## Re: Populate data from drop-down list selection

HI MC,

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

Punnam

13. ## Re: Populate data from drop-down list selection

Did you see post # 10?

14. ## Re: Populate data from drop-down list selection

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!

15. ## Re: Populate data from drop-down list selection

Where is the info in Data sheet coming from?

16. ## Re: Populate data from drop-down list selection

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

17. ## Re: Populate data from drop-down list selection

Hi MC,

To remove circular references using Error Checking-Circular reference.

Punnam

18. ## Re: Populate data from drop-down list selection

OK, put some more realist (but still non-sensitive) data into that table, so we can see what you are really working with. Those formulas you have in those columns are a complete mess, and dont really mean - or do - anything

19. ## Re: Populate data from drop-down list selection

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?

20. ## Re: Populate data from drop-down list selection

Hi MC,

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

Punnam

21. ## Re: Populate data from drop-down list selection

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

22. ## Re: Populate data from drop-down list selection

Hi Mc,

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

Punnam

23. ## Re: Populate data from drop-down list selection

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?

24. ## Re: Populate data from drop-down list selection

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

25. ## Re: Populate data from drop-down list selection

Brilliant! Exactly what I need. Thanks so much

26. ## Re: Populate data from drop-down list selection

Hi MC,

1) Can you let us know the post 25 is referring to whom?

Punnam

27. ## Re: Populate data from drop-down list selection

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

28. ## Re: Populate data from drop-down list selection

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1