• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying Data on Dashboard

J

Justin.T

New Member
Hi everyone,

I am no excel expert and this is my first time doing this (creating an excel dashboard).

There is a section in the dashboard with a combo box. The combobox allows the user to choose the different types of information (task id, task name etc ) they want to see sorted by task category. (example: they can choose to view misc. tasks or problems etc).

Now all of that information is stored in Worksheet A (in the same workbook). The difficulty I am having now is how to I get it all to work?

If you could provide me a sample by way of an excel sheet or the code I should be using that would help me heaps. Been looking at it for a day now.

Thanks heaps in advance.
 
Welcome to the forum Justin....

There are 100's of dashboards on chandoo..could please share the link of the dashboard that you are talking about..

Also, it would be great, if you share your sample data set and explain your requirement....
 
Hi Asheesh, thanks for the reply.

Please see attached.

What I would like to do is:
1. Have items from lstTasks appear in the main dashboard area (under the View)
2. The items visible will be dependant on what is selected in the combobox.
a. This will then show items related to the category chosen.
b. The items shown will also fall between a specified period

Thank you.
 

Attachments

  • Dashboardsample.xlsm
    44.3 KB · Views: 17
Hi:

Please find the attached file. I had to add an helper column since your drop down is different from the table data. I hope this is what you were looking for.

Thanks
 

Attachments

  • Dashboardsample.xlsm
    46.7 KB · Views: 17
Hi Nebu. Thank you. I will take a look now.

Nebu. I had a look and it's pretty cool but because I don't understand all the logic, I need to make some adjustments.

1. The dashboard needs to display task with dates that is >=Calculations!B5 and <= Calculations!B6. Currently if I am not mistaken it is just checking for dates =Z2

2. Could you explain what the numbers A14 etc are for? And did you manually populate the numbers in that column?

3. For cases when there are no results, how would I go about printing a test saying "No results found".

Thanks so much
 
Last edited:
Hi:

1.I have modified the formula as per your requirement please check and let me know.
2.The numbers from A14 are manually populated to nth values, for example A14 is 1 so it will populate the first instance for the combination we are searching for likewise , A15 is 2 it will give second instance and so on.
3. No results found is included in the formula now.

Thanks
 

Attachments

  • Dashboardsample.xlsm
    46.7 KB · Views: 11
Nebu said:
Hi:

1.I have modified the formula as per your requirement please check and let me know.
2.The numbers from A14 are manually populated to nth values, for example A14 is 1 so it will populate the first instance for the combination we are searching for likewise , A15 is 2 it will give second instance and so on.
3. No results found is included in the formula now.

Thanks
Click to expand...

1. Nebu thank you so, so much.

2. There is one more thing that I was looking at doing. The Key dates section, I would like to get that to auto populate as well with the information from the lstKeyDates range and where the date is >= Calulations!B5.

I've tried modifying the formula you used by without much success.

I created the following:
=IFERROR(INDEX(lstKeyDates[KeyDatesID],SMALL(IF(lstKeyDates[Key Dates]>=Calculations!$B$5,ROW(lstKeyDates[Dates])-4))),$v5)),"")

for cell Dashboard!W5 but the formula is wrong.

Your assistance is much appreciated. Thanks Nebu
 
Nebu said:
Find the attached...
Click to expand...
Hi Nebu, thank you so much.

I noticed that in the formula

"=IFERROR(INDEX(lstKeyDates[Key Dates],SMALL(IF(lstTasks[Dates]>=Calculations!$B$5,ROW(lstTasks[Dates])-4),$AC5)),"No results found")"

It references lstTasks[Dates]. Is there a reason for this? From what I've seen, it means that some of the key dates won't appear if I don't have more entries in lstTasks.

Ideally I'd like for the Key Dates section in the Dashboard to show all Key Dates from the lstKeyDates (named range) where the Key Dates is >= Calculations!$B$5.

Thanks again
 
I managed to change the code Nebu.

=IFERROR(INDEX(lstKeyDates[Key Dates],SMALL(IF(lstKeyDates[Key Dates]>=Calculations!$B$5,ROW(lstKeyDates[Key Dates])-4),$AC5)),"No results found")

So all good now.

The question I have now is how do I lock the main dashboard but still be able to use the dropdown box etc? Currently when I lock the sheet it says I need to unlock it for the cells to populate.
 
You must log in or register to reply here.
Back
Top