Excel questions (beginner)

brannsiu

Honorable
Apr 20, 2013
144
0
10,630
0
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

Splendid
Moderator
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.
 

brannsiu

Honorable
Apr 20, 2013
144
0
10,630
0



Any free alternative to Ms Access??
 

13thmonkey

Distinguished
Moderator
Jan 10, 2006
799
0
19,210
86
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

Honorable
Apr 20, 2013
144
0
10,630
0


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
Moderator
Jan 10, 2006
799
0
19,210
86
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
Moderator
Jan 10, 2006
799
0
19,210
86
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.
 

13thmonkey

Distinguished
Moderator
Jan 10, 2006
799
0
19,210
86


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

erikaray247

Prominent
Sep 22, 2017
15
0
560
0


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
0


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

erikaray247

Prominent
Sep 22, 2017
15
0
560
0


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
Moderator
Jan 10, 2006
799
0
19,210
86


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
0


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.
 
Thread starter Similar threads Forum Replies Date
henrytcasey Apps General Discussion 0
B Apps General Discussion 2
N Apps General Discussion 1
S Apps General Discussion 5
N Apps General Discussion 2
L Apps General Discussion 5
B Apps General Discussion 4
K Apps General Discussion 1
R Apps General Discussion 1
7 Apps General Discussion 37
J Apps General Discussion 11
E Apps General Discussion 15
L Apps General Discussion 5
S Apps General Discussion 1
B Apps General Discussion 2
M Apps General Discussion 1
indyitguy Apps General Discussion 8
grebgonebad Apps General Discussion 2
A Apps General Discussion 1
P Apps General Discussion 5

ASK THE COMMUNITY