- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Querying data from another excel file with 100+ sheets?
Posted on 2/10/20 at 8:22 am
Posted on 2/10/20 at 8:22 am
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.
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/20 at 8:58 am
5
Posted on 2/10/20 at 9:13 am to SlapahoeTribe
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.
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.
Posted on 2/10/20 at 9:57 am to Korkstand
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.
Posted on 2/10/20 at 10:01 am to Korkstand
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.
Posted on 2/10/20 at 5:45 pm to SlapahoeTribe
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.
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.
Posted on 2/10/20 at 10:15 pm to SlapahoeTribe
Does your work's Microsoft Office suite include Power BI?
Posted on 2/10/20 at 10:32 pm to SlapahoeTribe
I would look into R
Posted on 2/11/20 at 6:42 am to pjab
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.
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/20 at 6:44 am
Posted on 2/11/20 at 3:16 pm to SlapahoeTribe
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.
Posted on 2/12/20 at 12:14 pm to SlapahoeTribe
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.
Posted on 2/12/20 at 6:16 pm to WhiskeyThrottle
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/20 at 6:17 pm
Posted on 2/13/20 at 1:44 pm to CubsFanBudMan
quote:Hundreds.
How many rows are on each sheet?
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/20 at 7:06 pm
Posted on 2/13/20 at 2:06 pm to Korkstand
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.
Posted on 2/13/20 at 7:32 pm to WhiskeyThrottle
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...
Posted on 2/14/20 at 10:41 am to SlapahoeTribe
(no message)
Posted on 2/14/20 at 10:42 am to SlapahoeTribe
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?
Posted on 2/14/20 at 10:46 am to SlapahoeTribe
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 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/20 at 10:59 am
Posted on 2/17/20 at 10:50 am to LSU Delirium
quote:Thank you good sir.
Edit:
Here you go. This is assuming the sheet name is in column B: =COUNT(INDIRECT(B2&"!C1:C1000"))
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.
Posted on 2/17/20 at 12:37 pm to SlapahoeTribe
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)
Edit:
=MATCH("end of group",INDIRECT(B1&"!C1:C1000"),0)
This post was edited on 2/17/20 at 4:00 pm
Posted on 2/17/20 at 8:26 pm to GetMeOutOfHere
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.
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/20 at 6:40 am
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News