Posted by
Message
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
9999 posts

Querying data from another excel file with 100+ sheets?
At work I'm given a daily manifest with each region displayed on a different sheet in an Excel file. I would like to be able to have a master excel sheet that could ingest the new file each day and return the raw number of items from each sheet of the new file.

As an example I'm sent a file like - MondayMorningManifest12345.xls - then when I open it there's a sheet for each area that has to be supplied. Sheet 1 will be listed as "AAAA0001_lotsoflettersandnumbers" then sheet two will be "AAAA0002_lotsoflettersandnumbers" and this will continue until the AAAA area is full and will start with the BBBB area. There are a different number of A's and B's and so on each day. And each day there's a different number of items listed for each area. The items are listed vertically on each sheet - I know how to write in a formula in a cell to give me the number of items on each sheet, but I don't know how to make it look for the sheet, return the sheet name and list the total number of items on each sheet. I would like to be able to ingest the daily file into a master file, hit enter, and have it return a list like:

AAAA0001 | 17
AAAA0002 | 12
AAAA0003 | 54
(and so on)

The biggest issue I'm running into is the varying number of sheets, all having a varying name and a varying number of items (today there may be 12 A's, 30 B's, 10 C's etc. then tomorrow it may be 25 A's, 22 B's, 18 C's etc.). If there's a way to just add a tool or macro to the menu bar of excel that could generate a new sheet with my required data then that would be great - it doesn't have to be a separate excel file.

ETA- I've tried a nested IF function but that quickly becomes a damned nightmare.
This post was edited on 2/10 at 8:58 am


Korkstand
LSU Fan
Plaquemine, LA
Member since Nov 2003
17052 posts

re: Querying data from another excel file with 100+ sheets?
Get sheet by index rather than by name


Honestly though, you should immediately start the conversation with whoever needs to hear it about moving this data to a database rather than a clusterfrick of excel files.


mdomingue
LSU Fan
Lafayette, LA
Member since Nov 2010
11036 posts

re: Querying data from another excel file with 100+ sheets?
quote:

Honestly though, you should immediately start the conversation with whoever needs to hear it about moving this data to a database rather than a clusterfrick of excel files.




I love working in excel but this is as good a piece of advice as there is. DB is the place to store info for future access.


Replies (0)
Replies (0)
60
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
9999 posts

re: Querying data from another excel file with 100+ sheets?
quote:

Honestly though, you should immediately start the conversation with whoever needs to hear it about moving this data to a database rather than a clusterfrick of excel files.

I have tried.


pjab
LSU Fan
NOLA/Denver
Member since Mar 2016
4103 posts

re: Querying data from another excel file with 100+ sheets?
I would write a macro before using an index because someone will mess up the names of the arrays, it’s easier on the front end considering the amount of info, and should be easier to fix when the formatting gets jacked up.

Hopefully you get this file early and can run the macro just before your morning dump because your comp is going to lock up for awhile.


Oizers
LSU Fan
Member since Nov 2009
2095 posts

re: Querying data from another excel file with 100+ sheets?
Does your work's Microsoft Office suite include Power BI?


Replies (0)
Replies (0)
00
jeff5891
Chicago Cubs Fan
Member since Aug 2011
15075 posts

re: Querying data from another excel file with 100+ sheets?
I would look into R


Replies (0)
Replies (0)
02
WhiskeyThrottle
Member since Nov 2017
1322 posts

re: Querying data from another excel file with 100+ sheets?
I'm going to guess it's a SQL query generating the file through an app of some sort. So jacking up the sheet names or getting data types mixed probably won't happen.

If a developer isn't willing to put in about 15 min of work to save a business unit hours out of a day then there must either be some real high priority work going on or just a lazy IT dept. Either way, a macro running through sheet indexes is your bet option.
This post was edited on 2/11 at 6:44 am


CubsFanBudMan
Chicago Cubs Fan
Member since Jul 2008
3766 posts

re: Querying data from another excel file with 100+ sheets?
How many rows are on each sheet? Would you be able to fit all the data on one sheet? It probably wouldn't be hard to write a macro to copy each sheet onto 1 master sheet. I have a macro that combines the first sheet of all files in a directory into one sheet and adds the source file name on each row.


BoogaBear
LSU Fan
Member since Jul 2013
3068 posts

re: Querying data from another excel file with 100+ sheets?
quote:

have tried.


Do it yourself. Get MySQL set up for free on your local PC.

Then get a free ETL tools such as Pentaho, use it to read the files and put them in your local database.


Replies (0)
Replies (0)
01
TD SponsorTD Fan
USA
Member since 2001
Thank you for supporting our sponsors
Advertisement
BeepNode
The City of Central
Member since Feb 2014
1553 posts
 Online 

re: Querying data from another excel file with 100+ sheets?
quote:

if a developer isn't willing to put in about 15 min of work


15 minutes, eh? This is how projects fail.

How will users view and update data moving forward?

This post was edited on 2/12 at 6:17 pm


Replies (0)
Replies (0)
20
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
9999 posts

re: Querying data from another excel file with 100+ sheets?
quote:

How many rows are on each sheet?
Hundreds.


This is a damned frustrating problem. I can write a formula that will give me a list of sheet names (which changes everyday), and I can write a formula that will give me the cell value of a "match" from a particular sheet, but for some damned reason I just can't seem to be able to write a formula that will use an adjoining cell inside the formula.

Ex: In column A I have

1
2
3
...
...
...

In column B I have

=sheetname(A1)
=sheetname(A2)
=sheetname(A3)
...
...
...

And this gives me

AAAA0001_lotsoflettersandnumbers
AAAA0002_lotsoflettersandnumbers
AAAA0003_lotsoflettersandnumbers
...
...
...

Thats great, that is exactly what I want in that column, but what I want from the next column is for it to give me the result of a =match formula, like

=match("end of group",AAAA0001_lotsoflettersandnumbers!C4:C1000,0)

This works if I type out AAAA0001_lotsoflettersandnumbers and change it for each sheet, like

=match("end of group",AAAA0001_lotsoflettersandnumbers!C4:C1000,0)
=match("end of group",AAAA0002_lotsoflettersandnumbers!C4:C1000,0)
=match("end of group",AAAA0003_lotsoflettersandnumbers!C4:C1000,0)
...
...
...

And that will give me the result I'm looking for, but the problem is I want to be able to replace the AAAA0001_lotsoflettersandnumbers part (because this changes every single day) and have it change that to whatever sheet is listed in column B . Something like,

=match("end of group",WHATEVER THE CELL TO THE LEFT SAYS!C4:C1000)

For some reason this always returns and error. I've tried nesting it several different ways and it doesn't seem to like it. I can't even to get it to work if I simply write,

=match("end of group",sheetname(1)!C4:C1000,0)

Something about trying to put a variable before the ! is killing the formula.

This post was edited on 2/13 at 7:06 pm


skrayper
Alabama Fan
Member since Nov 2012
16830 posts

re: Querying data from another excel file with 100+ sheets?
quote:

Honestly though, you should immediately start the conversation with whoever needs to hear it about moving this data to a database rather than a clusterfrick of excel files.


This, a thousand times this.


Replies (0)
Replies (0)
00
GetMeOutOfHere
Member since Aug 2018
11 posts

re: Querying data from another excel file with 100+ sheets?
quote:

If a developer isn't willing to put in about 15 min of work to save a business unit hours out of a day 


Oh, you're one of those...


LSU Delirium
LSU Fan
Member since Aug 2013
164 posts

re: Querying data from another excel file with 100+ sheets?
(no message)


Replies (0)
Replies (0)
00
LSU Delirium
LSU Fan
Member since Aug 2013
164 posts

re: Querying data from another excel file with 100+ sheets?
I may mess around a bit with this later if I get some time, but unless I am misunderstanding, why are you using match? Didn't you say you wanted the total number of items, wouldn't that be a simple count?


Replies (0)
Replies (0)
00
LSU Delirium
LSU Fan
Member since Aug 2013
164 posts

re: Querying data from another excel file with 100+ sheets?
Also I believe you need to use the Indirect function for this to work.

This how a formula would normally look: =Sheet2!A1

If I put "Sheet2" into B2, then the formula will look like: =INDIRECT(B2&"!A1")

Edit:
Here you go. This is assuming the sheet name is in column B: =COUNT(INDIRECT(B2&"!C1:C1000"))
This post was edited on 2/14 at 10:59 am


SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
9999 posts

re: Querying data from another excel file with 100+ sheets?
quote:

Edit:
Here you go. This is assuming the sheet name is in column B: =COUNT(INDIRECT(B2&"!C1:C1000"))


Thank you good sir.

I'm still at a loss as to why the MATCH and a couple of other function won't allow me to work with them, but I can make the one you gave me work. Many many thanks.


LSU Delirium
LSU Fan
Member since Aug 2013
164 posts

re: Querying data from another excel file with 100+ sheets?
Match or any function will work, just imbed "INDIRECT(B2&"!C1:C1000")" wherever you need to refer to the sheet/range.

Edit:
=MATCH("end of group",INDIRECT(B1&"!C1:C1000"),0)
This post was edited on 2/17 at 4:00 pm


Replies (0)
Replies (0)
00
WhiskeyThrottle
Member since Nov 2017
1322 posts

re: Querying data from another excel file with 100+ sheets?
I’m a developer. 15 min is a bit disingenuous but even if it’s 2 hours of developer time that takes a business unit a couple of hours a day, seems like a no brainer to free the time up to be used elsewhere.

If the only change is to organize the data on one tab instead across 100 tabs, just copy the stored proc and create a new query that crams all the data on one sheet assuming it isn’t more than a couple hundred lines. Which it doesn’t sound like it is. It’s not like you’re recreating the data altogether. Just reorganizing how the data appears.
This post was edited on 2/18 at 6:40 am


Replies (0)
Replies (0)
00
first pageprev pagePage 1 of 1next pagelast page
refresh

Back to top

logoFollow TigerDroppings for LSU Football News
Follow us on Twitter, Facebook and Instagram to get the latest updates on LSU Football and Recruiting.

FacebookTwitterInstagram