Hello,
The Company where i was recently hired, gave me a .doc file in MS Word with a table and told me to make the totals. I don't know the MS Office suite, but they know less than me. First thing was to create the same table in excel, I have to respect this format table because is given by the local authorities.
Let's say that about 300 companies, every day few more, order daily croissant, chips, water, coffee and/or milk. A company can order today croissant, tomorrow chips and croissant, and the next day croissant, chips, water, milk and coffee at same address (usual) or different addresses (rare); or can order in the same day croissant at address 1 and milk at address 2.
Starting from the .doc file, I created a file in excel with 13 tabs: first tab is Total, then a tab for every month. Instead of 13 doc files - 12 months and a total, I have an excel with 13 tabs.
One file for chips, another for croissants and so on, 10 files, 130 tabs. In the end, i made a MS Access file where I pasted every month from every product. Copy/Paste 120 pages (January - December) is not very nice, but after that, was efficient to find what, who, when, how much from every company. Too bad that I can't update the Access file in real time; when i need it, I just copy/paste latest entries.
Example: if I am working at Chips for HNovember 2020, then I open two files: Chips2020 at tab Nov, and second file with Customers List in alphabetical order (300 entries), file with two columns: Company name and VAT Reg number; copy/paste that I need from Customers file to Chips2020; then Tab - Tab or Tab - Tab - Tab to jump over columns C and D, precompleted with same values, to E or F column needed; entering column G value, then picking up the date from an add-in calendar.
What I would like:
- by typing first three letters, to autocomplete (autofill) Company name (column A) and VAT number (column B), at the same time, and not from a dropdown list, ... just as Google display probabilities; then, pressing Tab, jump to column with Quantity (E, let's say). Last two columns may have different values which I can easily handle;
- second part that would save my time, would be how to automatically update MS Access database file with all entries from all 10 Products files, each with 12 tabs. It is important, but suppose to handle just 120 tabs, not 15.000 entries.
I can make a =Sum or a minus (A-B).
I dont'speak English, but I understand it. I wrote this post between 5 AM and 16 PM, mostly trying to translate in my head. Many useful articles found on Google didn't work. Or I am too dumb, or Excel considers that I don't use English system and I have to replace the comma "," in formulas with I don't know what... ";", "|".
Help to autocomplete, if it possible, the usual row would be apprecieted.
Formulas - vlookup, index or something else may be helpful, and I tried them, but everytime with errors. Maybe, because my Excel is set to use "dot" instead of "comma", because this is my country system. Or I am too dumb and did not searched enough.
I am asking for help because there are there are tens of thousands of entries.
Thank you.
The Company where i was recently hired, gave me a .doc file in MS Word with a table and told me to make the totals. I don't know the MS Office suite, but they know less than me. First thing was to create the same table in excel, I have to respect this format table because is given by the local authorities.
Let's say that about 300 companies, every day few more, order daily croissant, chips, water, coffee and/or milk. A company can order today croissant, tomorrow chips and croissant, and the next day croissant, chips, water, milk and coffee at same address (usual) or different addresses (rare); or can order in the same day croissant at address 1 and milk at address 2.
Starting from the .doc file, I created a file in excel with 13 tabs: first tab is Total, then a tab for every month. Instead of 13 doc files - 12 months and a total, I have an excel with 13 tabs.
One file for chips, another for croissants and so on, 10 files, 130 tabs. In the end, i made a MS Access file where I pasted every month from every product. Copy/Paste 120 pages (January - December) is not very nice, but after that, was efficient to find what, who, when, how much from every company. Too bad that I can't update the Access file in real time; when i need it, I just copy/paste latest entries.
Example: if I am working at Chips for HNovember 2020, then I open two files: Chips2020 at tab Nov, and second file with Customers List in alphabetical order (300 entries), file with two columns: Company name and VAT Reg number; copy/paste that I need from Customers file to Chips2020; then Tab - Tab or Tab - Tab - Tab to jump over columns C and D, precompleted with same values, to E or F column needed; entering column G value, then picking up the date from an add-in calendar.
What I would like:
- by typing first three letters, to autocomplete (autofill) Company name (column A) and VAT number (column B), at the same time, and not from a dropdown list, ... just as Google display probabilities; then, pressing Tab, jump to column with Quantity (E, let's say). Last two columns may have different values which I can easily handle;
- second part that would save my time, would be how to automatically update MS Access database file with all entries from all 10 Products files, each with 12 tabs. It is important, but suppose to handle just 120 tabs, not 15.000 entries.
I can make a =Sum or a minus (A-B).
I dont'speak English, but I understand it. I wrote this post between 5 AM and 16 PM, mostly trying to translate in my head. Many useful articles found on Google didn't work. Or I am too dumb, or Excel considers that I don't use English system and I have to replace the comma "," in formulas with I don't know what... ";", "|".
Help to autocomplete, if it possible, the usual row would be apprecieted.
Formulas - vlookup, index or something else may be helpful, and I tried them, but everytime with errors. Maybe, because my Excel is set to use "dot" instead of "comma", because this is my country system. Or I am too dumb and did not searched enough.
I am asking for help because there are there are tens of thousands of entries.
Thank you.
| chips_nov.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Company Name | VAT Reg | Ressource 1 | Rss. Code | Quantity | Empty | Order No. | Day | |||
| 2 | Abcggf | 246 | Chips | 12345 | 636 | 8697 | 11/17/2020 | date picker addon | |||
| 3 | Bcdef Address 1 | 123 | Chips | 12345 | 410 | 8698 | 11/17/2020 | ||||
| 4 | Cdefhh | 234 | Chips | 12345 | 413 | 8701 | 11/17/2020 | ||||
| 5 | Defhiohi | 345 | Chips | 12345 | 520 | 8704 | 11/17/2020 | ||||
| 6 | Efghf Hgdjo | 456 | Chips | 12345 | 305 | 8708 | 11/18/2020 | ||||
| 7 | Bcdef Address 2 | 123 | Chips | 12345 | 370 | 8709 | 11/18/2020 | ||||
| 8 | Fghhgf Jdfsfdsf | 567 | Chips | 12345 | 511 | 8712 | 11/18/2020 | ||||
| 9 | Ghijfddf | 678 | Chips | 12345 | 532 | 8714 | 11/18/2020 | ||||
| 10 | Hijfgdd | 789 | Chips | 12345 | 400 | 8718 | 11/19/2020 | ||||
| 11 | 4 Ijk Ssdfsd | 890 | Chips | 12345 | 540 | 8719 | 11/19/2020 | ||||
| 12 | Chips | 12345 | |||||||||
| 13 | Chips | 12345 | |||||||||
| 14 | Chips | 12345 | |||||||||
| 15 | 4637 | ||||||||||
Nov | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E15 | E15 | =SUM(E2:E14) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A6 | Expression | =" =$A$=""Row Labels""" | text | NO |
| A5 | Expression | =" =$A$=""Row Labels""" | text | NO |
| A3 | Expression | =" =$A$=""Row Labels""" | text | NO |
