Help on making a dynamic drop down and use the same in Powerpoint

[Abhishek_8895]

Hello Experts,

I appreciate some help.

What I am trying to do is - two things (explained below)

First thing:
I have a worksheet with some agenda that are same but for different domains/teams that are added as a separate worksheet.
I want to consolidate all of them in a single sheet and using a drop-down that is equal to the sheet names, I want to use the new sheet "One Click View" to automatically show the value based on what a user has selected from a drop-down. I researched a little bit that this maybe is possible using indirect formula or something but hard time and getting no where. So appreciate help from the experts and some guidance.

Second thing:

Post the new sheet is made "One Click View", I want to link it into a sharepoint slide where a similar thing will be achieved for presentation. So in that sheet, we will have the same drop down, and based on user selection the data should refresh.


I would sincerely appreciate some help here, as this is very critical and urgent for me.
Moderator note: You're late if something is u... for You
Example file is attached.

 

[Monty]

Hello Ashishek

First, for the consolidation of agenda items from different worksheets and displaying them dynamically based on a drop-down selection, you can use the INDIRECT function along with a drop-down list. Here's how you can set it up:

1. Create a new worksheet named "One Click View".

2. Set up a drop-down list in cell A1 containing the names of the worksheets you want to consolidate.

3. Use the `INDIRECT` function to reference the selected worksheet and display the agenda items accordingly.

Assuming your agenda items start from cell A2 in each worksheet, the formula in cell A2 of the "One Click View" sheet would be:

=INDIRECT("'" & A1 & "'!A2")

This formula will dynamically fetch the agenda items from the selected worksheet based on the drop-down selection.

For the second part, linking the "One Click View" sheet to a SharePoint slide, you'll need to ensure that the SharePoint slide can access the data from the Excel sheet. One way to achieve this is by embedding the Excel sheet into the SharePoint slide.

Let me know

 

[Abhishek_8895]

Hi Monty,

I am afraid but it doesnt seems to be as simple for me... I tried and it didnt work ...Alsi I tried with Vlookup, Xlookup etc but it only comes with 1 row information, not all. I attached the sheet for you, if you could check and let me know where is the issue... Also for the sharepoint slide...I know how to insert an object etc, but the same functionality that will be in excel thorugh the drop down - how to develop that.. If you are aware and can do a couple based on the attached sheet, then I can do the rest and will be very helpful.

 

[Monty]

Hello

Let mw guide you through the process step by step.

For your first issue regarding consolidating agenda items from different worksheets and displaying them dynamically based on a drop-down selection, let's try to troubleshoot it together:

1. Make sure the drop-down list in cell A1 of the "One Click View" sheet contains the exact names of the worksheets you want to consolidate.
2. Check that the agenda items in each worksheet start from the same cell. If they start from different cells, you'll need to adjust the `INDIRECT` formula accordingly.
3. Ensure that there are no spelling mistakes or extra spaces in the worksheet names and drop-down list entries.

If you've done all these steps and it's still not working, let me know which part is causing trouble, and we can debug it further.

For the SharePoint slide functionality, if you're able to insert an object or embed the Excel sheet, the drop-down selection should work similarly to how it does in Excel. Once the Excel sheet is linked or embedded, users should be able to interact with the drop-down and dynamically refresh the data displayed.

If you encounter any specific issues or need further guidance, feel free to ask!

 

[Abhishek_8895]

Hello Monty,

Basically what I have done is what maybe you can see in the attached sheet.

I created one sheet with all the different meeting names and gave them a table name.
In another sheet, I have created the drop down menu with names of the meeting and basis selection of that, I want the sheet to auto-populate the ITIL Framework, title and Description Column..

And want to retain the same when embed and link to slide in ppt.

I am afraid but the solution you proposed is not working and I have tried multiple things I know...like index/match, vlookup/xlookup etc but all failing...

Can you check the file attached and propose a solution that can work.. or we can even do an online call via any channel you prefer and try to collaborate

 

[Abhishek_8895]

Anyone?