Excel work book with 441 worksheets for tablulation

[arkanoid]

Hi all, hoping someone has a faster way to assist me with creating a report of daily sales.

Workbook 1

• has 441 worksheets
  • Each worksheet is for a day of the month i.e. June 3, 2022 and so on it increments through to July 17, 2023
  • The worksheets contain lines of items that were sold

Workbook 2

• Has 239 lines
  • Column A represents the inventory ID number
  • Column B is the description of each ID in column A
  • Column C auto sums each row from column D through to column PK
    • NOTE that Columns D to PK are the days of the month and year

I have been manually going through each daily worksheet from workbook 1 and manually entering the data into their respective rows and columns in to workbook 2.

There has to be an easier way to gather this information for summary.

Anyone have any ideas or solutions for this?

Thanks to all that help in advance.

 

[Joe4]

Welcome to the Board!

You are absolutely using the wrong tool for the job here. What you have is a relational database, and as such, it would be much better to use a Relational Database program like Microsoft Access or SQL. They are built for precisely this sort of thing (a ton of inter-related data).

 

[arkanoid]

Ok thanks for that.

This data was extracted from a MySQL database to a csv file.

Would I then be able to connect to the MySQL database to retrieve the info?

 

[Joe4]

If the data is already in MySQL, then you can use that as your data source and may not need to bring it into Excel.
Here is how you can link MySQL to Microsoft Access: Connecting Microsoft Access to MySQL via ODBC Driver

Alternatively, you can use Power Query within Excel to do database-type operations on the data (leaving it in MySQL).
See this: Connecting Excel to MySQL via ODBC Driver

 

[arkanoid]

If the data is already in MySQL, then you can use that as your data source and may not need to bring it into Excel.
Here is how you can link MySQL to Microsoft Access: Connecting Microsoft Access to MySQL via ODBC Driver

Alternatively, you can use Power Query within Excel to do database-type operations on the data (leaving it in MySQL).
See this: Connecting Excel to MySQL via ODBC Driver

Thanks again.

I'm using office365 and do not have an ODB C option.

I do have the mySQL option and I have installed the MySQL connector(MySQL :: Begin Your Download) but keep getting this error message

 

[Joe4]

I have never connected to MySQL from Access or Excel/Power Query before, so I am afraid I cannot help you with that part.

But did you click on the "Learn more" link in that message box to see what it tells you?

Also, you should be able to use ODBC in Office 365. Might require some configuration though.
Check this out: Administer ODBC data sources - Microsoft Support

There are a lot of other articles out there on the subject that can be found with a Google search.

 

[arkanoid]

I have never connected to MySQL from Access or Excel/Power Query before, so I am afraid I cannot help you with that part.

But did you click on the "Learn more" link in that message box to see what it tells you?

Also, you should be able to use ODBC in Office 365. Might require some configuration though.
Check this out: Administer ODBC data sources - Microsoft Support

There are a lot of other articles out there on the subject that can be found with a Google search.

Yes that's where I go the info regarding the MySQL connector for download.