Hello.



Presently, at work, I am working on a document in which people will ultimately select several layers. Part of getting the right data is identifying one sheet that is to allow users to select which sheet to use in order to properly redirect data. Is there any chance I can do this? Most of my searches have turned up showing me how to add such a dropdown to the ribbon bar using VB script. Any help would be much appreciated.

It looks like this is along the lines of what I want. There are just two issues. #1: Even when I copy the steps as written, I get a "#NAME?" error. #2: It doesn't explain whether or not I can put these into a drop down, as I would prefer.

Can you post a sample workbook indicative of what you're trying to do and how you're trying to do it ?

Like a doctored screenshot? Sort of hard to post a sample workbook with something that I'm trying to figure out how to do.

Anything that will let us know what you're trying to achieve more so than how you're trying to achieve it will help us help you.

Currently, don't know what you mean by 'drop down'.... there's combo box or data validation

I've currently attached the Excel workbook that I am trying to use for this. If you click on the first tab, named Card, you can see that I've organized some groups with names. Ideally, there would only be one cell which would contain all of these sheet names, and, depending on which one you select (from a dropdown), it would change the list values in a second dropdown which would be based off of the Person row in each of the sheets.

I'm also running into another issue where Ignore Blanks isn't working. I tried including a sample dropdown.

Attached Files

mwcil-Resource-Contact List.xlsx (61.2 KB, 10 views)

I would use much simpler approach:

You need a sheet Organizations with columns like
OrgName, OrgPhone, OrgSite, OrgState, OrgDistrict, OrgCommunity, OrgStreetNo, OrgSuiteNo, OrgZIP

and you need a sheet Persons with columns like
PerName, PerPhone, PerMail, PerState, PerDistrict, PerCpmmunity, PerStreetNo, PerSuiteNo, PerZip, PerOrganization, PerTitle, PerNotes (you can have additional columns from Orcanization sheet calculated also, when you feel you need them).

On sheet Persons, in column PerOrganization the organization is selected from Data Validation list, based on dynamic named range defined on base table in sheet Organizations.

For table Persons, autofilter is set.

User activates sheet Persons, and sets autofilter for column PerOrganization. After that user sets autofilter for column Person (only persons from filtered organization are displayed) to wanted person(s). The info for only selected person(s) is displayed.

The same functionality you described, only 2 sheets needed, and only 2 steps to find wanted person (or any number of wanted persons).


And when the displayed row is too wide, then you can add a Report sheet. But this will complicate things considerably! On Report sheet you select organization from one Data Validation list, a person from selected organization from another Data Validation list, and all info about selecte person is displayed in more compact form. In this case you must have some hidden columns in Persons sheet with formulas to enumerate rows with PerOrganization equal with one selected on report sheet, and to enumerate all rows in Persons table, and a hidden sheet, where a list of all persons from organization selected on Report is calculated (you have to use INDEX function on Person column with number ot table row with #'th number of person of this organization in Pesons table as 2nd parameter) . On this list you create a dynamic Named Range to select person on Report sheet.

My advice is - try 2-sheet design at start.

The report sheet is absolutely a necessary function of the sheet for the purposes for which I'm creating it. I'll take the suggestion into consideration to reduce the number of sheets and see if I can make it work with the filters. However, the end-goal of the entire file is for people to be able to select a person and get formatted contact information that can be printed, applied to labels, etc...

Perhaps this will assist in populating drop downs.
Saved as .xlsm with code in ThisWorkbook module, the "Card" sheet module and Module1.

Attached Files

mwcil-Resource-Contact List.xlsm (88.3 KB, 11 views)

That's more or less along the lines of what I'm looking for in terms of a dropdown. I would then use that information to pull up the contact info and format it on the sheet in a manner similar to a business card. Something which I should be able to do once I understand what's going on here. Did you happen to use VB for this? How can I look at the code if you did?

Quick Google search got me the shortcut to look at the VBA and confirm my suspicions. So, at this point, the question is, is it even possible to reference those values from within a cell on the sheet? If not, then I'll have to learn the VBA necessary to format as I mentioned and display it on the sheet somewhere. I don't have a problem doing that, mind you, but it's a lot more involved than just referencing the data that is pulled up from the drop downs in-cell.

As it is, I may try to adjust the VBA code to work with the simpler "Organization" and "Person" worksheets as suggested by ArviLaanemets

Data validation... in cell drop downs.
All VBA is in the 'Card' sheet module.

Attached Files

mwcil-Resource-Contact List_2.xlsm (78.5 KB, 9 views)

Here is an example how I would do it.

Sheet Hidden, as you can guess, will be hidden.

I started with grouping organizations like in your example (OrgTypes), but dropped the idea and went with organizations instead. The sheet and columns remained, but aren't used.

On Persons sheet, last 2 columns are meant to be hidden too.

On report sheet, you can drag fields with formulas into any position on sheet you want. I prepared only some fields to give you the idea. and of-course you can display organization info on report sheet too (use VLOOKUP).

Attached Files

ContactsExample.xlsx (20.7 KB, 16 views)

So. No VBA here, it seems, which would definitely be more manageable for me. However, when I checked Data Validation for Report, B1 and B2, I found references to a column name that I can't seem to find within the document?

I don't have Excel on my home computer, and for some reason I can't connect to another one through VPN currently, so I can't look what formulas are used on Report sheet. But in general, the formulas use Table and Table Field names, and Named Ranges. So probably a Named Range was the one to confuse you.

Of-course you can post the formula here too (for me it's night currently, so probably I will see it tomorrow morning anyway, but it will be nice to know which formula and which part of it needs to be commented).