How to lookup multiple sheets of an excel file


#1

Hello, is there a way to read multiple sheets from an excel file at the same time? I put in a list of sheet numbers but its only taking the first line item. What am I doing wrong?
Thanks


#2

It looks like it’s working to me. A single sheet would only have 3 levels - you have 5. You don’t even need to use list levels. Just using a list of sheet names will return each sheet properly.
image


#3

ok well I want make sure I transpose this right. also why cant i read the columns now?’


#4

Your list levels are off. And the shorthand List[0] won’t run on sublists. You need to use GetItemAtIndex and list levels to get ColumnA for all sheets. Right now you’re just getting Item[0], Item[4], and Item[8] - but you only have 6 sublists.


#5

So do I need to fix my List.transpose level to 3 also? I thought it looked like it was working…?


#6

Ok I know I am trying to do this the hard way but cant I take every column form the list that I need? Why isnt this working for me? logically it looks like it should work.


#7

It depends on how you plan to use your lists later on. You don’t even need to use transpose if you just want to get the column data. (Using Transpose @2 doesn’t actually do anything. I’m just showing it for reference.)


#8

You’re just not managing your lists correctly.

First option: If you transpose your sheets to group by column you can then get each column by index. This will return separate columns for each sheet.

Second option: Without transposing your sheets, you can get each column by index, but this will return values by row. (You’ll likely have to do more list management with this option).


#9

How do I drill down one level in a code block? Isnt there a number that I need to put into this list? I forget how it works…


#10

List levels don’t work with Design Script shorthand. You have to use GetItemAtIndex.


#11

You can get the sublists like this, if this is what you mean:

image