Pull Data from different tab using Data Validation

[jc553570]

Hello,

I have a bit of an issue. I am good at Excel, but I am working on something that is way out of my league. I am completely stumped.

I have an input sheet which uses cascading of data validation.The user would select a company and from that, a separate start date and end date drop down box will populate with the dates that are associated with that company. Some companies have more data and dates available, which is where my problem lies. I am now trying to pull data from a different sheet in the workbook, based on the selections made in the data validation drop down boxes.

Is that possible? If you are not understanding my problem, or would like to see the spreadsheet, please reach out to me personally. I am trying to get this issue resolved as quickly as possible.

I appreciate your time in advance.

Thanks!

[Pete_UK]

I think you should attach a sample workbook which shows what you have and what you want to do with it. The FAQ describes how to attach a file to one of your posts.

Pete

[jc553570]

Pete,

I appreciate the quick response. I have attached an example of a sheet that looks similar to the one that I am working on. The issue that I stated in my first post still holds true.

Any assistance that you can offer is greatly appreciated.

Thanks again!

[Pete_UK]

See attached file. I've used column G in the INFO and DROP_DOWNS sheet as a helper column (coloured blue), with this formula in G2 of the INFO sheet:

Please Login or Register  to view this content.

which is then copied down beyond your data to allow for new data to be added (the hyphens help to show this). The formula checks to see if the ID is the same as that chosen in the Drop_downs sheet, and if the date fits within the start and end date and if so it allocates a number in sequence so that each matching record is uniquely numbered.

I put this formula in G6 of the drop-downs sheet:

Please Login or Register  to view this content.

and this reports which row in the INFO sheet those sequential numbers occur.

This formula is in A6:

Please Login or Register  to view this content.

which is also copied across to E6 with appropriate formatting applied to each cell. This formula retrieves the data from the appropriate column and from the row of the INFO sheet given in column G. I've copied the block of formulae down to row 30, as can be seen from the hyphens. You can hide the helper columns if you don't want to see them.

Make changes to B1:B3 to see the data display change appropriately.

I've also tidied up your definitions of the named ranges A and B slightly using Name Manager, but these seemed to be working okay.

Hope this helps.

Pete

[jc553570]

Pete,

Thank you SO much for your help. I was able to mess around with your formulas a bit and make it fit to my actual data, and it works like a charm. The only other question I would ask is that if I were to keep adding more data to this sheet, can I simply keep dragging the formulas down further and they will still work?

The actual data I have will be updated most likely every 3 months or so, and I want to be sure that it will continue to work in the future.

Thanks again, it really means a lot that you were able to help!!

[jc553570]

Pete,

I apologize but I actually have a couple more issues. I thought that I would be able to figure them out, but I guess I overestimated my Excel abilities.

So after the user makes their selection of dates and the data is populated into the DROP DOWNS sheet, I need to then make projections for the monetary PMPM amount using the GROWTH function. If this makes it any easier, the max amount of months that I will ever need the projections for is twenty months.

The issue that I am running into is that when the user selects dates from the drop down boxes, the last cell will be different depending on what dates are selected. That being said, I am unsure of a formula that I can use that will tell excel to find the date of the last cell, and then project out data after that date.

Another issue that piggy backs off of this is that the user will be selecting the number of months that they want projections for, and so I don't know if I should create another data validation box on the DROP DOWNS sheet which allows them to choose the number of months they want projections for, or if there is an easier way.

The last issue is then graphing the actual data and the projections. I obviously know the manual way of selecting the data that you want graphed and using chart manager, but I was curious if there is some type of formula that can be written to automatically populate a graph based on the actual data chosen and then the projected data as well.

Any assistance that you can offer is greatly appreciated. You have been such an awesome helper.

Thanks!!

[Pete_UK]

... The only other question I would ask is that if I were to keep adding more data to this sheet, can I simply keep dragging the formulas down further and they will still work?

Yes, that is why I copied the formula in the INFO sheet down to row 44 (well beyond your data) and the formulae in the DROP_DOWNS sheet to row 30 - the hyphens indicate that you have copied the formulae down far enough. You could also add this formula in G1 of the DROP_DOWNS sheet:

=COUNT(INFO!G:G)

which will tell you how many records you should expect to be displayed.

As for your follow-up request in Post #6, I don't really understand what you want to do. Perhaps you can provide a mock-up?

Hope this helps.

Pete

[jc553570]

Pete,

I have attached a sample workbook which I hope explains better what I am trying to do. I used the same example book that I you had originally attached and simply added on some notes to the DROP DOWNS sheet. I also added another sheet called PROJECTIONS which also has notes on it. I hope these comments are a little bit more meaningful to you as I have been going crazy trying to figure this out.

Please let me know if you can offer any assistance. If it still is not making sense to you, please get back to me and I will try to explain it in a different way.

[Pete_UK]

I think the attached is what you are looking for, though I've not used the GROWTH function before, so I'm not really sure what you are trying to do.

I've not used your Projections sheet - instead, I have amended the Drop_downs sheet by moving the MATCH column into a new column A (and thus moving all the other columns to the right), and I have set up C4 with data validation so that you can only choose whole numbers from the range of 1 to the maximum given in cell C4 (this is 20 minus the number of displayed records).

The GROWTH function in column H (coloured blue) applies to the displayed records, but using C4 to specify the number of projected months, the Projected Dates and Growth are shown in columns J and K (coloured pale yellow), which are copied down to a maximum of 20 rows. Conditional Formatting is used to hide errors in columns H and K.

The GROWTH functions make use of INDEX and MATCH to dynamically adjust the ranges depending on the number of records displayed, and have to be array-entered (with Ctrl-Shift-Enter) into a range.

Hope this helps.

Pete

[jc553570]

Pete,

That worked like a charm.

Is there any formula that automatically populates a graph/chart based on data in the spreadsheet? Or do you simply have to go through the chart builder?

Let me know!

jeremy

[Pete_UK]

I usually select the data, then click on Insert | Graph, choose the graph type and then play about with the various settings. There is no single formula that will create a graph for you.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete