Excel Question

[DrJohnZoidberg]

Pivot Tables make it really easy working in Excel with large data sets. I've used them with 500 000 rows before, no issues.

 

[visrot]

Pivot Tables make it really easy working in Excel with large data sets. I've used them with 500 000 rows before, no issues.

 

[Mars]

Lol. You still struggling?

You would have to purchase a copy of Office 2010. You could always "purchase" a copy online

I have got a version I acquired online a while ago, but if it works I will need a licence for it before I use it in my business.

I'm getting the hang of pivot tables, but I need users to draw daily reports and thats where it comes short.
There is probably a way. Just have to figure it out.

 

[DrJohnZoidberg]

If you're feeling adventurous you could always look at using a SQL database. MySQL is free and there is plenty of support online - learning curve is a bit steeper than Access though but with phpMyAdmin it is not that tricky.

 

[AlphaBravo]

No **** sherlock.

ngwe23 was only checking what your current proficiency level is

 

[Mars]

If you're feeling adventurous you could always look at using a SQL database. MySQL is free and there is plenty of support online - learning curve is a bit steeper than Access though but with phpMyAdmin it is not that tricky.

Could I use that in conjunction with excel?

 

[DrJohnZoidberg]

Could I use that in conjunction with excel?

Yes you can. Would most likely take you a while to get to grips with it and would only really be useful if you need to work with a lot of data and work with it often otherwise sticking to Excel would probably be the better option./

 

[SirFooK'nG]

Do you keep the data in spreadsheets and query from access?

Normally no, I like to store my data in the access tables. But linking back and forth is pretty easy...

 

[Mars]

Yes you can. Would most likely take you a while to get to grips with it and would only really be useful if you need to work with a lot of data and work with it often otherwise sticking to Excel would probably be the better option./

I'll give it a go and see how hard it gets.

 

[InvisibleJim]

I have got a version I acquired online a while ago, but if it works I will need a licence for it before I use it in my business.

I'm getting the hang of pivot tables, but I need users to draw daily reports and thats where it comes short.
There is probably a way. Just have to figure it out.

Where is your data coming from? If it is in an ERP or accounting system which uses a SQL database as the back end, you can query the database directly from Excel (using MS Query) and return the results to a worksheet as a table or a pivot table. You can then refresh the spreadsheet to update it.

To do this, you will need an ODBC datasource set up in Windows to access your database.

From your initial post, the information you want to extract isn't too complicated so you should be able to pull it straight into Excel.

 

[Mars]

Where is your data coming from? If it is in an ERP or accounting system which uses a SQL database as the back end, you can query the database directly from Excel (using MS Query) and return the results to a worksheet as a table or a pivot table. You can then refresh the spreadsheet to update it.

To do this, you will need an ODBC datasource set up in Windows to access your database.

From your initial post, the information you want to extract isn't too complicated so you should be able to pull it straight into Excel.

Na, its the other way around. The data is currently captured into spreadsheets. One for each day of the month. then I have to go and draw this info into a central place so I can process it.

I have fomula's doing this for me, but its problematic and somehow they keep managing to stuff my formulas up. If you cut and paste anything in excel it changes the formulas to the new location. Over all the sheets its a nightmare to track a problem and fix it.

my new idea is to use a form to put the data into one large sheet and then use pivot tables to report the daily figures into a report for each day.
Then I can process the data any way I want to without having to try and consolidate it from all the other sheets.

 

[ichigo]

IE: I need to draw any information that have the same invoice number into one row on a different sheet if the row has a certain code.

From:

|#| Invoice   |  Item   |  Qty    |
|2| 1234      | 6763    |  3      |
|2| 1234      | 6764    |  4      | 
|2| 1234      | 6766    |  7      | 
|2| 1234      | 6769    |  5      | 
|2| 1235      | 6763    |  3      |
|2| 1235      | 6764    |  4      | 
|2| 1235      | 6766    |  7      | 
|2| 1235      | 6769    |  5      |

So sheet "2" will show the following info:

| Invoice   |  Item   |  Qty   |   Item   |  Qty    |    Item   |  Qty   |    Item   |  Qty   |
| 1234      | 6763    |  3     |  6764    |  4      |   6766    |  7     |   6769    |  5     | 
| 1235      | 6763    |  3     |  6764    |  4      |   6766    |  7     |   6769    |  5     |

I would like it to be automated somehow but I have no idea where to start.

Theres functions you could use without having to do a pivot table or anything fancy.

 

[Jabberwocky]

Na, its the other way around. The data is currently captured into spreadsheets. One for each day of the month. then I have to go and draw this info into a central place so I can process it.

I have fomula's doing this for me, but its problematic and somehow they keep managing to stuff my formulas up. If you cut and paste anything in excel it changes the formulas to the new location. Over all the sheets its a nightmare to track a problem and fix it.

my new idea is to use a form to put the data into one large sheet and then use pivot tables to report the daily figures into a report for each day.
Then I can process the data any way I want to without having to try and consolidate it from all the other sheets.

1 big table with a date column... simpler is better, how many columns to you have?, just thinking using a form would needlessly complicate it.

 

[InvisibleJim]

Na, its the other way around. The data is currently captured into spreadsheets. One for each day of the month. then I have to go and draw this info into a central place so I can process it.

OK, you do need a database then. I strongly disapprove of Excel being used to store important data. Access is easiest if you are new to databases but isn't so great if you are using it for multiple users (make sure you have a regular backup). Try Base from LibreOffice/Openoffice if you don't want to buy MS Access (it is not as feature rich as Access but will do the job) or you can go the MySQL route as suggested but that is a little more involved.

I have fomula's doing this for me, but its problematic and somehow they keep managing to stuff my formulas up. If you cut and paste anything in excel it changes the formulas to the new location. Over all the sheets its a nightmare to track a problem and fix it.

Read about relative and absolute referencing in Excel help and/or Google. It sounds like you need absolute referencing in some cases and a means to stop other people from stuffing these up!

my new idea is to use a form to put the data into one large sheet and then use pivot tables to report the daily figures into a report for each day.
Then I can process the data any way I want to without having to try and consolidate it from all the other sheets.

Congratulations, you've just made a logical leap towards using a database. You are thinking in terms of organising your data correctly and separately from your presentation.

 

[Mars]

1 big table with a date column... simpler is better, how many columns to you have?, just thinking using a form would needlessly complicate it.

It would have to be two tables, one for basic invoicing information and one for stock. I would need two because I want to have a new line for each stock item, but I dont want to repeat the basic invoice information.

I have been told over and over that an accounting package would be better than using a spreadsheet for this, and I have had a look at both pastel (which we already use) and quickbooks, but neither of them can do what I need without serious customisation.
We use pastel for the books, but this workbook allows me to handle our unique stock control issues as well as giving me a quick look at the numbers without having to go into pastel.

OK, you do need a database then. I strongly disapprove of Excel being used to store important data. Access is easiest if you are new to databases but isn't so great if you are using it for multiple users (make sure you have a regular backup). Try Base from LibreOffice/Openoffice if you don't want to buy MS Access (it is not as feature rich as Access but will do the job) or you can go the MySQL route as suggested but that is a little more involved.

Read about relative and absolute referencing in Excel help and/or Google. It sounds like you need absolute referencing in some cases and a means to stop other people from stuffing these up!

Congratulations, you've just made a logical leap towards using a database. You are thinking in terms of organising your data correctly and separately from your presentation.

I know I need a database. Ive known for years. I just dont have the skills I need to get it going. I'm going to give access a try first (again) I think.

 

[Jabberwocky]

It would have to be two tables, one for basic invoicing information and one for stock. I would need two because I want to have a new line for each stock item, but I dont want to repeat the basic invoice information.

I have been told over and over that an accounting package would be better than using a spreadsheet for this, and I have had a look at both pastel (which we already use) and quickbooks, but neither of them can do what I need without serious customisation.
We use pastel for the books, but this workbook allows me to handle our unique stock control issues as well as giving me a quick look at the numbers without having to go into pastel.



I know I need a database. Ive known for years. I just dont have the skills I need to get it going. I'm going to give access a try first (again) I think.

another nice little excel trick, if you have a stock table:
Highlight your item column in your invoice table, click data->validation->allow: list
source: highlight your stock items.
it will create a nice little drop-down when you select the cell, to choose which stock item you want to use, and it will only allow items that are in the other table