populate list based on two selected conditions

Greetings Chaps

This is more a plea for ideas than a specific solution at the moment because I do enjoy floundering around.

I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for.

This worksheet posesses amongst others; two columns titled location and sublocation.

I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions)

This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.

A sample worksheet is available if required.

Hope you can help.

Paul

Originally Posted by simpo067

A sample worksheet is available if required.

That would be a good idea

See attached.

Steps taken:

1. Created Lists containing unique locations and sublocations in columns CA and CB of Sheet2.

2. Named these ranges "dynamically" through Insert|Name|Define (you can add to the lists in CA and CB and the dynamic range will expand to include those).

Named Ranges:

LocationUnit:

Please Login or Register  to view this content.

LocationSubUnit:

Please Login or Register  to view this content.

3. Created drop down lists in A1 and B1 of Sheet1 using List and the dynamic named ranges.

4. Created a helper column that concatenates columns L and M of Sheet2.

5. Created a count in E1 of Sheet 1 using formula:

Please Login or Register  to view this content.

which counts number of matches in Sheet2 based on criteria in A1 & B1

6. Created formula in A3 of Sheet1:

Please Login or Register  to view this content.

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER.. and then it can be copied down as far as you want down the column.

This fetches matching items from column A of the Sheet2.

You may need to change the range bottom from $1000 to whatever you need...but you must reconfirm with CSE keys before copying down.

7. Created formula in B3 of Sheet1:

Please Login or Register  to view this content.

normally Entered and copied down and across remainder of the table.. no need for row adjustments... this fetches remaining items without using array formulas.

Syntax:

=Offset(reference,rows,columns,[height],[width])

e.g

=OFFSET(Sheet2!$CB$1,1,0,COUNTA(Sheet2!$CB:$CB)-1,1)

Reference = Sheet2!$CB$1
Rows = 1
Columns = 0
Height = COUNTA(Sheet2!$CB:$CB)-1
Width = 1

so we are offsetting from Sheet2!$CB$1 , 1 row (to exclude column header) and 0 columns...starting really at Sheet2!$CB$2, and taking a range equivalent in size to the number of text values in Sheet2!$CB:$CB (utilizing the Counta() function to get that number) and since we don't want to include the title in Sheet2!$CB$1, we subtract 1 from that size (or else we would also be including a blank in the drop down list).. the last 1 refers to the column width that we are extracting (we only want things from 1 column, column CB to show up in the list).

No Problem

Actually, I may have mislead a bit.. the excluding the title part was done when we offset 1 row...the CountA()-1 part doesn't exclude the actual physical header, it excludes the fact that the header was part of the count... so if we don't put the -1 in there it will give you that extra blank at the bottom of the drop down list....

hope it is a little more crystal clear now

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save