Excel spreadsheet help

[target64]

What I am looking to accomplish:
1) I want to automatically move data from several worksheets to master workbook. Currently the number of worksheets is 12, it will eventually go to around 60.
2) I need this data to automatically go to the first empty available row in the workbook

Issues at hand:
1) I have limited knowledge of excel
2) I have no knowledge of macros or VBA.

Desired solution:
1) Be shown / taught how to accomplish this task.


If this is a task that you know how to do and are willing to help or show me how, p​lease let me know. If this is something that requires compensation please pm me so we can discuss it. Thanks

 

[GodFearinGunTotin]

I don't know how to do your particular task off the top of my head but when I come across things like this, Google is your friend. Chances are somebody else has already done what you're asking about and have posted up a how-to on how to get it done. I'm no expert on VBA or more than simple macros but I've found articles that have basically walked me through it.

 

[eldirector]

https://www.youtube.com/watch?v=HBjNlTmLQRY
Or, Google "excel summary sheet"

Are all of your worksheets in the same file? Makes it tons easier.

 

[target64]

My work sheet is a bill of lading form. I make several of them each day. I need them put numerically in to a master data work book. Moving the data between work book or worksheets I can do. Getting the data to populate automatically on the next empty row, is where I am having the issue.

Master form


daily work sheet. Areas in yellow I need to move. But keep in numerical order.

 

[tatic05]

Google search VLOOKUP function. I would think you could arrange them in specific orders with that function as well.

 

[eldirector]

The columns in your two sheets don't match 100%.

Question: do you need the TOTAL number of pieces (Quantity) for that bill of lading to show in the summary? Will there ever be more than one row of data on the bill of lading?

What you really need is a super simple database with a couple of tables and a couple of views/forms. We can probably beat Excel into submission, though.

 

[PistolBob]

You could create those forms in Access Database and be all set.

 

[PaulF]

You could create those forms in Access Database and be all set.

1000 times this.

The amount of data you are pushing around is too unwieldy for a spreadsheet. While you should certainly do what you are doing now, you should be looking for a better long-term solution.

MS Access is easy to learn, and can be customized to perform all your basic data keeping tasks. It is actually amazingly powerful if you get into it and push the boundaries a bit. A few years ago I built a lan-based "application" in Access to handle order entry, inventory, and order fulfilment, among a few other tasks...it was pretty cool.

Anyway, get your employer to give you a licence for MS Access (if you don't already have one), go down to Half Price Books and pick up a couple books on MS Access, VBA, and SQL (even the "for dummies" ones are full of good info), and start re-creating all your forms in Access. Your existing spreadsheets will become the foundations for the tables in Access, and you will eventually migrate your live data into your new database once you are happy with its performance.

It sounds like a ton of work, and it is...but MS Access makes it easier than you think. They even have a macro where you can start by importing the Excel spreadsheet into Access, and it will build the initial tables, some ugly forms, and the initial relationships and queries required to make them work. You can go in and start customizing from there.

For what you are describing, a database is the correct tool.

 

[PaulF]

So, it looks like those pages are not in the same worksheet. We should start there.

If you have both worksheets open you should be able to drag and drop the "Bill of Lading" worksheet into the "RECEIVING TEXT" Workbook. Just click and hold on the tab, and drag it from one workbook to the next. A dialog box should appear asking how you want to handle the copy/transfer.

If that doesn't work, you'll have to import the worksheets into the "master" workbook using the pull-down menus.

Once you have all your forms in the same workbook it is just a matter of writing a couple of formulas.

 

[glank09]

PM sent.

 

[Double T]

You should be able to highlight the cells and add a function to the defined cells (a1+a2+a3) for your summation cell. For carrying over page to page, I forget how to do that.

 

[searpinski]

1000 times this.

The amount of data you are pushing around is too unwieldy for a spreadsheet. While you should certainly do what you are doing now, you should be looking for a better long-term solution.

MS Access is easy to learn, and can be customized to perform all your basic data keeping tasks. It is actually amazingly powerful if you get into it and push the boundaries a bit. A few years ago I built a lan-based "application" in Access to handle order entry, inventory, and order fulfilment, among a few other tasks...it was pretty cool.

Anyway, get your employer to give you a licence for MS Access (if you don't already have one), go down to Half Price Books and pick up a couple books on MS Access, VBA, and SQL (even the "for dummies" ones are full of good info), and start re-creating all your forms in Access. Your existing spreadsheets will become the foundations for the tables in Access, and you will eventually migrate your live data into your new database once you are happy with its performance.

It sounds like a ton of work, and it is...but MS Access makes it easier than you think. They even have a macro where you can start by importing the Excel spreadsheet into Access, and it will build the initial tables, some ugly forms, and the initial relationships and queries required to make them work. You can go in and start customizing from there.

For what you are describing, a database is the correct tool.

Access can be finicky though. Make many many backups.

Edit:

I read your post in more detail. You don't necessarily need a database, but it would make things easier. As long as you keep the sheet locations (that you're pulling from) static, you can definitely make this work.

Your options are:

1. Make a master worksheet. Have it "check" several static worksheets for new data and auto-populate your master. This would require a (fairly simple) VBA script.
2. Enter all data into a database (access or other) and set your spreadsheet to pull reports from that database. This is also simple, but would require you to change your recording method. Databases are better for handling large amounts of data, pulling reports or allowing multiple users to access it. This method would require using Pivot tables in excel or a minimal knowledge of SQL.

 

[MDave]

I agree with Pistolbob,PaulF and Searpinski. You could work something up to meet your needs quickly in Access or many other data base programs.

I would bet a search for example databases would get you pretty close to where you want to be.

Access can be used to create Excel spreadsheets or other reports that could take the place of your bill of lading quite readily. It will take a bit of work but if you are willing to look at your project from a different angle the results can be very satisfactory.

I would add that a database solution will have fewer moving parts when done and will be easier to maintain over time.

 

[target64]

Thanks for all the suggestions.