Check out some similar questions!

Lozza · Jun 30, 2008, 04:28 AM

Hi,

I am not sure if I am in the right area but I need to ask about excel 2003. I have a simple question which I do believe there is an answer but I have tried various options to no avail. Does anyone know where I can ask expert advise about excel 2003. Many thanks for any advise given.:)

Curlyben · Jun 30, 2008, 04:32 AM

What's the problem you are having ?

Lozza · Jun 30, 2008, 04:43 AM

I have a master sales ledger, a master invoice template. I can link them both but only once. I need to be able to link the invoice (each time I input) on to a different row and as I only have a basic knowledge of excel I cannot find out how. Hope that makes sense

JBeaucaire · Jun 30, 2008, 08:29 AM

Every cell in an Excel spreadsheet can be linked to another source spreadsheet. The source spreadsheets don't have to be open. You can link and unlink cells at will.

Can you give more detail about what you're doing and/or post an example of what you want to do (exactly) that you CAN'T seem to figure out? More detail is better, up through and including a complete walkthrough of what you would like to see happen and the result to be.

I use Excel03 every day.

Lozza · Jul 1, 2008, 02:49 AM

Hi JB

Thank you. I have a master sales ledger (sheet 1), invoice template (sheet 2). I want to input different invoices on sheet 2, 1 at a time using the same template but I want all the info on the invoices to link to master sales ledger on different rows. I can link one row no problem but as soon as I input a different invoice it links on the same row. I only have a basic knowledge of excel but I am sure there must be a way of doing this. If there is not can you advise me so I don't waste anymore time on it and I can look for an alternative. I have attached a example (a very basic draft) for you to see. Hope this all makes sense.

ScottGem · Jul 1, 2008, 05:53 AM

Hmm I thought I had answered this.

Frankly, I think you would be better off using a database rather than a spreadsheet, but if you want to use Excel you can.

The way I would handle this is with three separate areas (probably 3 separate sheets). The first area would be your data entry. The second area would be your data storage and the third area would be your invoice.

You enter your data into the data entry area. The invoice template is then linked to cells in the data entry area. When you print the invoice you copy the data to your storage area. If you ever need to reprint an invoice you just copy it from the storage area into the data entry area.

JBeaucaire · Jul 1, 2008, 07:56 AM

I believe you have it backwards. You want to do it the other way around.

So the Sales Ledger on sheet1 is really a table listing all the activity for a certain period, then you want to be able to click on INVOICE 1 and print that page having it reference any of the entries you want from the Sales Ledger page?

That's doable. In Excel, the way to have the invoice1 fill itself out is to use a Lookup Table reference. All you have to do is change the invoice # and it will find that in the ledger and fill out the rest of the info.

Here's my take on that. On the printable INVOICE1 page, all you have to do is enter an invoice # and the rest fills itself out from the Sales Ledger data. There is also a reference on the Invoice page showing the current highest invoice # in the ledger. It won't print.

On the Sales Ledger, just start w/the Customer and the invoice # will fill itself in.

The functions used in this project are VLOOKUP and MAX.