Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Help with Excel calculations

  • 01-05-2009 1:16pm
    #1
    Closed Accounts Posts: 36


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


Comments

  • Registered Users, Registered Users 2 Posts: 3,498 ✭✭✭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


  • Registered Users, Registered Users 2 Posts: 3,375 ✭✭✭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.


  • Closed Accounts Posts: 36 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.


  • Moderators, Politics Moderators Posts: 41,240 Mod ✭✭✭✭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.


  • Registered Users, Registered Users 2 Posts: 656 ✭✭✭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.


  • Advertisement
  • Closed Accounts Posts: 36 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.


Advertisement