Help with Excel calculations

diydan

I am doing up a spreadsheet where each of my product categories is on a seperate tab/ sheet. The idea is that the user can go down through the products in each tab and put their shopping list together.

As the quantity of each product in each tab is entered an invoice is generated on the first tab. Only the products which are not 0 are put on the invoice.

I have all the information and fuctions in place in each tab but don't know how to go about writing the functions correctly to get the updated totals from each tab to the invoice page. I tried but at each attempt I got a message saying stuff about circular errors.

Can't anyone point me in the right direction...

Lu Tze

Howdy,

Circular references usually mean you are somehow referencing the cell you are putting the formula, or are referncing another cell in your formula which already references the one you are inputing the formula into. Eg (Cell A1 "= B1", putting "=A1" in cell B1 creates a circular reference).

you will just have to trawl back through to find where it is though the formula auditing toolbar helps with that i find

kmick

Post up the spreadsheet and we can have a look. Circular errors mean you are doing something like
A3=A3/A4 i.e. you are referencing a cell withing the formula if that makes sense.

diydan

Sorry I was out of action for the last week or so. I haave attached an example of what I am trying to do

The purpose of the file is as a calculator. i.e choose the quantity of each product ineach category and on the first sheet an invoice summary is generated automatically. I'm not pushed on the invoice number or anything like that.

The invoice summary will only include products with 1 or more in the quantity field.

Does anyone have any ideas.

Seth Brundle

You won't be able to use formulae to do this as it stands. You would need VBA code writing to the first sheet when the Worksheet_Change event is triggered.

davidoco

see the attached.

I see what you are trying to do.

idea 1 - different approach

The tab "quote" is where an order could be generated. (using Lookups which are hidden - do unhide to see the workings). The tab Product ID is where you enter your products (there are a lot of columns there which you may not need. Customers would need to know your product ID when ordering this way and need to fill it in to autopopulate the price.

idea 2 Along the lines of what you are attempting.

In same sheet I have put a sheet called "picklist". Supply that to customer who completes column "I" "your order quantity". When you get it back using AutoFilter (Data,Filter,Autofilter) under the order quantity column just select "non blanks" - you could amend the top of that sheet to look like an order form ready for printing.

diydan

Thanks a million Davidoco. I hope you didn't spend too much time on it. It looks like you did. This will give me a starting block to work from. I will be going for your second option too. It seems much more flexible.

Thanks again.