Christopher has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel VBA Introduction course
Excel - using data validation to pull selected data from one she
Hi all,
Was wondering if someone could help me please.
I'm working on a doc for a colleague. They are not at all Excel literate! They want a few things (all of which I've solved - apart from one). The one I'm a little confused about is this. They want to do something which is effectively like performing a query within Excel. They want all data where a particular field value = "x" (whatever 'x' may be). Basically like auto-filtering the data, but they want a 'bottom-up' rather than 'top-down' type of search.
What I've done is built a series of dependent data validations (so that each one is filtered based on the previous one). Upon choosing the final filter I want to collect all the data (that match the value in the final filter) from one sheet and make it appear on the sheet as a report.
I can't use a simple vlookup to get the data as that will only return the first record and repeat it ad nauseam.
Is there a way of doing this *without* using VBA (as the doc will be used outside my company and with orgs that don't allow macro-enabled workbooks), and without using Microsoft query (as the doc will be shared around so won't be able to link to a location on a network drive)?
Was wondering if an index/match type thing might work...
I hope all that makes sense!
Thanks in advance for your help.
Chris
RE: Excel - using data validation to pull selected data from one
Hi Chris,
Thank you for the forum question.
Sorry but I am not sure I understand exactly what you want.
I have some questions which I hope will clarify your question. How have you built a series of dependent data validations (so that each one is filtered based on the previous one)? I normally does this with a combination of range names and the Indirect function.
The advanced filter can be used to get the result from a filtered list in another destination, but unfortunately it doesn't work in a shared workbook. You can copy the result from a filtered list by selection visible cells. To do this you will need to select the result and then press down ALT ; and the CTRL C. This will copy visible cells.
If you want me to have a look at the workbook you can send it to info@stl-training.co.uk
It will make it easier for me to understand exactly what you need.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel - using data validation to pull selected data from one
Hi Jens,
Thanks for the reply.
I built a set of data validation lists sharing an aspect of data each and then used the formula below to get from each of the lists (in data validation).
=OFFSET('Data validation'!$C$1,MATCH(C2,'Data validation'!$C:$C,0)-1,1,COUNTIF('Data validation'!$C:$C,C2),1)
Etc...
To get the final data, I'd usually do this sort of thing in SQL in Microsoft Query in Excel (or build it into a form that performs a query in Access), but seeing as it's for outside my work that doesn't seem like an option. Can I use Microsoft query inside a workbook to reference itself?
I'll mail it over, but I need to edit the doc to remove personal data.
Thanks,
Chris
RE: Excel - using data validation to pull selected data from one
Hi Chris,
I have tried to Google Microsoft Query internal data and I can only find VBA solutions or PowerPivot or using the advanced filter.
Sorry I cannot give you a better answer.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
