PDA

View Full Version : Excel problem



bk T
12-03-2005, 09:13 PM
I'm creating an Invoice using Excel (Office 2003). I've a 'Product Code' column and would like to limit the numbers entered in this column, say from range 1001 - 1100. If the user enters a number other than this range, it should reject the entry and gives an error message.

How do I do it?

Cheers

Parry
12-03-2005, 09:49 PM
You can use data validation for this. First select the cells this is to apply to then Data|Validation from the menu. There are 2 options for what you require...

If you will only be entering whole numbers
In the Settings tab select the Whole number option in the Allow box, and in the Data option boz select Between and choose your range of values. The other tabs allow you to set input and error messaging.

If you will be entering any number (ie including decimals)
In the Settings tab, Allow Box select the Custom option, and in the formula box enter the following formula where C1 is the first cell in the selected cells you are doing validation on.
=AND(C1>=1001,C1<=1100)

bk T
12-03-2005, 10:22 PM
Thanks, Parry, but, where is the 'Settings' tab?

godfather
12-03-2005, 10:30 PM
With the cell(s) selected Data - Validation - then you have 3 tabs one of which is settings.

bk T
12-03-2005, 10:31 PM
Sorry, Parry. Found it. ;)

bk T
13-03-2005, 10:48 AM
Have more or less completed the Invoice creation. One final (hopefully) problem, I can't get Excel to generate the Invoice Number automatically. Is it possible at all for Excel to perform this task? Say, my first (starting) no. is 1001 and the after closing this invoice, when I open the next Invoice, I want Excel to generate the Invoice no in sequence, i.e. 1002.

Can this be done?

Cheers

Parry
13-03-2005, 11:18 AM
Yup, all depends upon how you have arranged your data to the best approach. If you have a separate invoice sheet and another sheet where you store all the invoice details, then you could use a macro that will copy the values from the invoice sheet to your data sheet then clear the details in the invoice and assign a new number ready to go for the next one.

If your using a list approach then you can use the Max formula to find out the highest invoice number so far and add one.

bk T
13-03-2005, 02:58 PM
Yup, all depends upon how you have arranged your data to the best approach. If you have a separate invoice sheet and another sheet where you store all the invoice details, then you could use a macro that will copy the values from the invoice sheet to your data sheet then clear the details in the invoice and assign a new number ready to go for the next one.

If your using a list approach then you can use the Max formula to find out the highest invoice number so far and add one.


Thanks, Parry. It sounds quite complicated to me. Which is the simpler way to achieve this (of the 2 approaches mentioned above)? Would appreciate it if you could elaborate a little more details.

I'm currently using the separate invoice sheet approach but problem may arise when more and more invoices are created. Is there a way to save the completed invoice and open a fresh invoice of new entries? I have my database stored in another sheet with all the inventory details like product code, item description price, etc. and the Invoice is linked to this sheet to retrieve the information.

Can you please explain a little further about using the second approach, i.e. 'List' approach. it sounds interesting.

I can email a copy of my 'draft' copy of the Invoice to you if necessary.

Thanks

Parry
13-03-2005, 03:59 PM
Hi again, either way you choose there is a need to have details go into the data list as values and not formulas. To do this in a list I was meaning your not using an invoice page, or if so your still entering the values directly into the data. Its the invoice which has formulas to find the latest invoice and bring in details, and solely used for printing purposes.

You have an invoice sheet used to input details so the list is a bit clumsy. Although it sounds complicated with a macro its not really that bad.

Heres an example.
Invoice sheet tab name is "Invoice". Data sheet tab name is "Data"
The Invoice sheet has the following cells you want to copy to the data sheet
B1 = Invoice #
B2 = Customer #
B3 = Product ID
B4 = Price
B5 = Quantity
B6 = Amount

The data sheet has the following column headings
A = Invoice #
B = Customer #
C = Product ID
D = Price
E = Quantity
F = Amount

So you want the macro to send B1 value from the Invoice sheet to the next blank row(x) in column A in the Data sheet, B2 to Bx, B3 to Cx etc then when finished add 1 to the value of cell B1 and clear the contents of B1:B6. Finding the next blank row is very easy (one line of code) as is placing the values and adding one to the invoice number. In fact all of this could be done with the macro recorder and using a max formula to find the next invoice number.

Yes you can send me the book if you want and I will comment the code so you can see whats happening. Afterwards, you should be able to adjust the code yourself if you make changes and add new fields etc.