VBA how to call cell values using combo box drop down value and make a progress bar

[cecilia_is_the_best]

Hello,

I am new to VBA and trying to make a dashboard. My raw data has the columns:

Requester Name; Request Date; Comments; Status

So under requester name there will be a name and I would like to make a dashboard where you select one of the names and then it shows all of the data associated with that. How do I get a combo box to allow me to select a name (from the Requester Name column), and then display the other data from that row?

For example if I select Name#1, I want the dashboard to display the info they need to know, ex. request date, comments, etc. I would also like to somehow make a progress bar showing the status (not started, in progress, on hold, completed), and I don't even know how to start this.

Any help would be appreciated! I've tried reading/googling and trying to figure it out but I'm just not sure where to start or how to find info on exactly what I'm trying to do.

Thanks!

Cecilia

P.S. The purpose of this is because my raw data contains a lot of other columns that would be irrelevant to the person listed under "Requester Name". I want it to be easy for them to open the spreadsheet, go to the dashboard, and select their name from a drop down and show all their open requests, relevant info, and their status.

[oeldere]

So under requester name there will be a name and I would like to make a dashboard where you select one of the names and then it shows all of the data associated with that. How do I get a combo box to allow me to select a name (from the Requester Name column), and then display the other data from that row?

Why not used the built in filter?


You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[CK76]

This will largely depend on your data size.

If it's relatively small (say about 1k rows), I'd probably just use formula along with data validation.
If dashboard is to pull info from another sheet and raw data has mid size (10k~ rows), use advanced filter.
See link for detail.
http://www.contextures.com/xladvfilter01.html
http://www.contextures.com/exceladvancedfiltervba.html

If raw data is 100k+ rows then I'd be inclined to use some other methods.
1. If Raw Data can reside in separate workbook, VBA using ADO
2. If Raw Data must reside in the same workbook... Use array and/or dictionary object
3. If using Excel 2010 or 2016, use PowerQuery

At any rate, it would help if you can upload sample workbook demonstrating your set up and expected output.

[cecilia_is_the_best]

Here is an example. Also, the amount of data is very small. Definitely less than 1000.

I see what you're saying about just simple functions.

My issue is that they wanted it to look really nice and be super easy to use - open worksheet, select name, see visual (aesthetically pleasing) info and more importantly status.

I thought that VBA would make this easier.

Especially since the requester name may have several rows, and i want it to display the data from each row. Like Request #1, Request #2, and the status from each.

And some of the cells have a lot of text so I thought maybe the cells would need automatic resizing and I thought that could only be done in VBA? I'm not sure.

Cecilia

[cecilia_is_the_best]

Sorry I think that was a bit confusing. I have tried to clarify below.

I don't know how to get from selecting a single name from a drop down to display ALL of their requests? (See in example2, i want to select name 1 from a dropdown and get it to show name 1's request #1 and 3)

But the comments section vary a lot in size and I would like it to auto size. Also not sure how to make the progress bar. Also not sure how to filter the dropdown to exclude duplicates.

[oeldere]

Dashbord!B16 shows a datavalidation cell

See Data => datavalidation => source => =data!$A$1:$A$10

See the attached file.

[CK76]

In most instances, I wouldn't recommend showing comments/long text in a dashboard.

That sort of defeats the purpose of dashboard. Having said that, you can apply certain formats using VBA.

See attached sample.

FYI - You don't have enough info for me to do progress bar.

[cecilia_is_the_best]

Thanks that's really cool!

Is there any way to get the drop down to find the names from the raw data instead of an creating another list? More names get added all the time and it would be nice if it automatically included them.

What do you mean about the progress bar? I just want something like... Not started would be at 0%, On Hold (33%), In Progress (66%), Completed (100%)

[CK76]

To create list, there are multiple methods.

1. Use formula to extract unique (fill 100 or so cells with it) and create dynamic named list.
Formula to extract unique names: (In lists sheet "A2")
=IFERROR(INDEX('Raw Data'!$C$2:$C$1000,MATCH(1,INDEX((COUNTIF(A$1:A1,'Raw Data'!$C$2:$C$1000)=0)*('Raw Data'!$C$2:$C$1000<>""),0),0)),"")
Copy down.

Dynamic Named Range:
=Lists!$A$2:INDEX(Lists!$A:$A,COUNTIF(Lists!$A:$A,"?*"),)

2. For progress bar... I really wouldn't recommend it if it's just to show status of those 4 status. It adds nothing of value to dashboard, but visual fluff, and only detracts from actual data.

Having said that, easiest method is to use formula and conditional format. See attached.