Excel questions (beginner)

brannsiu

Distinguished
Apr 20, 2013
146
1
18,635
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!
 
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.
 

13thmonkey

Distinguished
Jan 10, 2006
797
3
19,210
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

Distinguished
Apr 20, 2013
146
1
18,635


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

Distinguished
Jan 10, 2006
797
3
19,210
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

Distinguished
Jan 10, 2006
797
3
19,210


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

erikaray247

Prominent
Sep 22, 2017
15
0
560


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.
 

erikaray247

Prominent
Sep 22, 2017
15
0
560


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

Distinguished
Jan 10, 2006
797
3
19,210


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

Prominent
Sep 22, 2017
15
0
560


Got it. let's bury the hatchet
 
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.