dynamic inputs of sheets in excel file on SharePoint.

Question

Hello all:

Here is my dilemma

I have an excel file that has 50 sheets and continues to grow each week as the user adds a new sheet.  The good news, the new sheet has the date of creation. 👍

The not so good news...the file is on a SharePoint site, and for sakes of working with a server, I'd prefer not to synch the SharePoint to my One-Drive.

I Know how to read in the sheets of this excel file, sort them by the sheet name and sample only the top 1 sheet to get the name of the most recent sheet created.

BUT...here is where I come to a halt, and seek the help from the community...

Now that I know what sheet I need to bring in, how do I bring in just that one sheet?

Thanks.

-prpatel.

prpatel · Accepted Answer

Hi all:

I was able to solve it.

Incase you're interested, here's what I did....

I brought in just the sheet names and filtered out the sheet that I wanted.

Next, I brought in (using another SharePoint connector) all the sheets, and also the sheet name (obtained from the options).

Then appended the sheetname that I want to this dataset, then filter out where "Sheet name from aggregated dataset" = "Desired sheet name".  Then I just drop a few columns that I do not need.

That worked for me...hopefully it works for someone else as well.

Let me know.

-prpatel.