Select same data range from multiple Excel sheets (in the same file)

Question

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

After a lot of searching, and looking at the above page, I can work out how to import data from multiple sheets OR I can work out how to import a particular range from a single sheet (both of which I have done before). However, it seems to be missing the step I need – I want to import data from multiple sheets in a single file (58 tabs, to be exact), and from every sheet, only import the data in cells B15:B53 (as there is a lot of “junk” on the rest of the sheet that I need to ignore).

I know – as always – this will have a very simple Alteryx solution, but I just cannot work it out! Please help me, wonderful Alteryx experts 😊

To add - I have worked out the Dynamic Input required to pull in the sheet names, edited to add a field that indicates "Sheet1", "Sheet2", deselected all the irrelevant columns  etc. but there is still junk in column B (i.e. cells B1:b14) that I don't want to import. I'm sure I could (perhaps!) work out a way to delete it all after it's been collated in to a single data list - it just doesn't seem very elegant and I'm assuming there must be a way to just only bring in the specific data I need.....

TC

TaniaC · Answer

Hi @DavidSta - thanks so much for your suggestion (and apologies it has taken so long to come back to you - I have had some unexpected time off, away from the computer for a change!)

I wasn't entirely able to apply your solution, although I have no doubt it was probably the correct one - I just couldn't work my way around getting all the data in from every sheet, but only specific ranges in every sheet.  Most definitely my lack of competence rather than any deficit in your proposal ;)

I did, however, manage to find a (possibly far less elegant) solution of bring ALL the data in from all sheets and then gradually dumping out all the bits I didn't need. I still feel like I have probably used 15 steps when 4 would do, but it works.

Thanks again for taking the time to provide a solution - so happy to know I can always find help here in this great community.

All the best

TC

DavidSta · Answer

Hi @TaniaC,

I think the part you are missing is how the correct path should look like for your case.

When loading a range it looks like:

ExcelFile.xlsx|||SheetName$B15:B53

So you "simply" add the range after the Sheet Name separated by a "$".

With this you should be able to load your Excel Sheets with the specific range as well.

Please find attached a sample Workflow.

Best regards,

David

DynLoadRange.yxzp