Excel questions (beginner)

[brannsiu]

Hi I am very new to Excel

I'd like to create a table in Excel where I can put in some information like Name, Gender, Age, Job etc...
and then I'd like it to update the information to a table on another sheet or another excel file

How can I do that?

Another example is , I am creating an invoice in Excel for my customer. Whenever I fill in the information like invoice number, dates, total price of the items, price paid etc... then it will transfers those information to another sheet or another excel file to keep a record of the all the invoices I've created. And whenever I choose a particular invoice in the table list I can go back to the full details of the invoice.

How to do that?

Thank you for all the advice!

 

[getochkn]

You can do it, but you need to know VBA o do some programming to be able to find the next row in the table, etc.

Access is better suited for this or there are free open source invoice software and even fully online ones that would be much easier.

 

[why_wolf]

Sounds like you want a basic database system. Access is what Microsoft offers for that.

https://products.office.com/en-us/access

 

[brannsiu]

Sounds like you want a basic database system. Access is what Microsoft offers for that.

https://products.office.com/en-us/access

Any free alternative to Ms Access??

 

[13thmonkey]

You can do it in excel, but only for a limited number of lines at a time.

Have your data table in lines say 10-100, in lines 1-5 have the table headers the same as in the main table. Point your invoices etc. at lines 2-5. Within the main table have a 'selection' column, i.e. a column that allows you to state which rows you are interested in, so maybe use 1,2,3,4,5 to indicate which line you want it to appear as.

So in lines 2-4 you put some logic that looks at the main table (Vlookup), and looks to see if the selection flag is set, if it is then you pick up that row, this'll be a vlookup for each cell.

VBA would be neater and more flexible however. As this method is limited to 1 row, or a maximum of N rows, but is difficult to grow readily.

 

[brannsiu]

You can do it, but you need to know VBA o do some programming to be able to find the next row in the table, etc.

Access is better suited for this or there are free open source invoice software and even fully online ones that would be much easier.

free open source invoice software??

I tried to find a lot of invoice software before going to Excel, but I can';t find anything open source. What's the name of that? Thanks!!

 

[13thmonkey]

Actually there are two ways of thinking about this.

The way you've described, take the invoice and then transfer that data to the data table and storage.
or
Apply the data to the data table and then create the invoice from that (which is how i'd do it), the data is never moved, just looked at when needed.

 

[13thmonkey]

Something that will work:
https://docs.google.com/spreadsheets/d/1PztApkjgvr4xecSk9UozG-5z0-mdyrSDCNSQ27kbNAk/edit?usp=sharing

 

[brannsiu]

Something that will work:
https://docs.google.com/spreadsheets/d/1PztApkjgvr4xecSk9UozG-5z0-mdyrSDCNSQ27kbNAk/edit?usp=sharing

can I download the template??

 

[13thmonkey]

If you want, only created it for you, not sure how your actually 'download' in Google docs, all the formulae will work in excel, so copy and paste into a book with the same sheet names might be best.

 

[erikaray247]

Hello, I have this type of file that you need. some programming add into the developer option. Check this may it will helpful for you. https://www.techonthenet.com/excel/macros/project_explorer2013.php

 

[13thmonkey]

Hello, I have this type of file that you need. some programming add into the developer option. Check this may it will helpful for you. https://www.techonthenet.com/excel/macros/project_explorer2013.php

So someone with basic excel knowledge, should start with VBA?

 

[erikaray247]

Hello, I have this type of file that you need. some programming add into the developer option. Check this may it will helpful for you. https://www.techonthenet.com/excel/macros/project_explorer2013.php

So someone with basic excel knowledge, should start with VBA?

Its depend on your IQ I was also the beginner but I have done it. If someone guide and if you really have interest then I think you will do it definitely not in one time but this is possible.

 

[13thmonkey]

Oh it's possible, but getting more advanced with Excel would be useful first, don't you think?

 

[erikaray247]

Oh it's possible, but getting more advanced with Excel would be useful first, don't you think?

Well, what do you suggest? What do you have in mind?

 

[13thmonkey]

I've given him a sheet above that does what he needs, with no vba required.

 

[erikaray247]

I've given him a sheet above that does what he needs, with no vba required.

Great! But he I think he wants 2 separate excel sheets, not in one sheet. you use Vlookup and it will be got hanged after huge data but in VBA its works perfect. What you think?

 

[13thmonkey]

I've given him a sheet above that does what he needs, with no vba required.

Great! But he I think he wants 2 separate excel sheets, not in one sheet. you use Vlookup and it will be got hanged after huge data but in VBA its works perfect. What you think?

on another sheet or another excel file

He's got something now that'll work, if he's got enough invoicing to do that the vlookup fails (I've run it on >100k rows), then use index match, if that fails that's brilliant cause he's selling loads of stuff and can afford a proper solution, but in the meantime this works and he can concentrate on his business, rather than learning to code so that he can run his business (unless he's a coder, which he's not).

 

[erikaray247]

I've given him a sheet above that does what he needs, with no vba required.

Great! But he I think he wants 2 separate excel sheets, not in one sheet. you use Vlookup and it will be got hanged after huge data but in VBA its works perfect. What you think?

on another sheet or another excel file

He's got something now that'll work, if he's got enough invoicing to do that the vlookup fails (I've run it on >100k rows), then use index match, if that fails that's brilliant cause he's selling loads of stuff and can afford a proper solution, but in the meantime this works and he can concentrate on his business, rather than learning to code so that he can run his business (unless he's a coder, which he's not).

Got it. let's bury the hatchet

 

[Alabalcho]

I would look on that question from another angle: If one can afford to buy Microsoft Office (so he/she can use Excel), for sure that one should be able to buy a basic accounting package. For example, cheapest QuickBooks is $200. Excel is not meant to be "database" and "enduser" solution.

 

[13thmonkey]

^ I actually agree with this, I run my business off excel, as it was easier for me to build something in excel than learn to use quickbooks, the accountant does the official work. Do whatever you have to to run your business, spend as little time as possible administering your business, and as much time as possible running it, selling, buying, making, marketing etc.

There are subscription accounting packages like xero, so you don't have up front cost.

 

[getochkn]

Setup a LAMP severs (websever with PHP, MySQL, Apache, etc) on an old machine, can be from 15 years ago. - Free
Install something like http://opensourcebilling.org/ - Free

You could also then make the webserver ports open and do invoicing from anywhere on the net for free.

 

[13thmonkey]

Setup a LAMP severs (websever with PHP, MySQL, Apache, etc) on an old machine, can be from 15 years ago. - Free
Install something like http://opensourcebilling.org/ - Free

You could also then make the webserver ports open and do invoicing from anywhere on the net for free.

I might just use that

 

[USAFRet]

If it were me, I'd do something in Access+Excel.
But that's what I do. 150,000 peoples worth of broken Access and Excel problems eventually float up to me...:lol:

If I were an Excel beginner, I wouldn't even consider it.
Quickbooks, or the above LAMP stack.