Excel – Embed Excel Chart with Drop-down control into powerpoint

I think this might be a similar issue as with the Ref Edit Control, see MS KB 213776 for details about this control which you can use for VBA UserForms. (I make extensive use of this control in my VBA solutions.)

Microsoft has provided a workaround for the problem with the Ref Edit Control here: MS KB 291110 and I can imagine this will also help you with the Application.InputBox-problem.

Edit: and after posting my answer I noticed that this question has been asked AGES ago... :-(

If you are allowed to download and install the standard (and free) Microsoft product "Power Query", you can. More information about the use of Power Query can be found here. It is available from Office 2010 and up. It provides you with a lot of tools to select and merge data from different sources.

In your case, do the following:

• Create a new Excel workbook.
• Using Power Query, load the first action list to your Excel sheet .
• Use "Append" to append the other action lists to the resulting table. At the end, you should have a (dynamically linked) combined list of all your action lists.
• Using Power Query you can delete and rearrange the columns, sort the data and set filters on the columns. I recommend that you do the selection, sorting and filtering here in the Power Query, because if you do this in the final table in Excel, you need to re-apply all those filters every time you update the table.
• Save the workbook and close Excel.
• Start Powerpoint and on the Insert tab on the Ribbon choose Object and select "Create from file" and select the newly created Excel workbook with the combined action lists.

If you need to update the data, open the Powerpoint presentation and open the Excel object en click on the Refresh button and your data is updated. That's just a few clicks to update all of the action lists (cool, right?!)

This solution works for Office 2010 and 2013.

Finally there are your requirements about spanning multiple sheets and saving a static copy. There is no out-of-the-box answer to that, but I'd like you to consider to do this:

• Keep a "master" version of your Powerpoint with the reference to the Excel file in it (hint: make this file read-only).
• Every week, update the data with a few clicks as described above.
• Copy the Excel table (even if it is too big for one sheet) and paste it as a picture (Paste - Picture or Paste - Paste special... - Microsoft Office Graphic Object).
• Now you can duplicate the entire PowerPoint slide with the picture on it and use the cropping tools (select the picture and choose on the Ribbon tab Picture - Format - Crop) to select the right items on each sheet.
• You can reduce the size of the PowerPoint presentation by selecting a picture and go to the Ribbon: Picture - Compress Pictures and choosing the appropriate settings.
• Finally the sheet with the original Excel data can be deleted from this week's version of the presentation.

It is not really automated, but it keeps a non-editable version of your data in the presentation and it fulfills all of your requirements. The advantage of this approach is that all of the work can be done from the Powerpoint presentation itself.