+ Reply to Thread
Results 1 to 12 of 12

getting the value of the cell that has a forumla and avoid circular warnings

  1. #1
    Registered User
    Join Date
    05-11-2015
    Location
    Middle Earth
    MS-Off Ver
    MS2007
    Posts
    5

    Lightbulb getting the value of the cell that has a forumla and avoid circular warnings

    I have a table that I had set up to figure out the amount that will cost for a certain user. The amount will change depending on the number of time this item is chosen (i.e. one item cost $60 and if 5 people chose this item, it will cost $12 per person because the cost is split between them). Then I want to have the sum of the amount added up.

    Take a look here: http://prnt.sc/eo3tlb for what I have so far.

    P5 and Q5 are the items A and B wants to order. The cost is split between them for a total of $30.50 per person. # of request will let me know simply how many people are requesting for this item.

    What I want to do is instead of having the "x" in P5 and Q5, it turns into the cost of the item so I can do a sum (seen in P1 and Q1) of the total cost for all items for each person.

    The only issue is each time i try different things, I either get an error or a circular warning. I get why I'm getting a circular warning but I don't know how to get around it.

    For the # of request, I have this code: =COUNTA(C5:Q5) and for the Cost, I have =61/(COUNTA(C5:Q5). Any ideas on how I can get the value of the cost on the cell it's counting?

    I've tried searching for a solution but nothing has worked so far.

    Any help would be appreciated. Thanks!

  2. #2
    Forum Contributor
    Join Date
    01-19-2013
    Location
    Las Vegas, NV
    MS-Off Ver
    Office 365 2016
    Posts
    214

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    You will be better off posting an example file for us/someone to use/work on.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  4. #4
    Registered User
    Join Date
    05-11-2015
    Location
    Middle Earth
    MS-Off Ver
    MS2007
    Posts
    5

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    Sorry! First time posting. Thanks for the instructions. I wasn't sure what to do next after uploading but here's the link I grabbed from the upload:

    https://www.excelforum.com/attachmen...1&d=1490625505

    Hope this works and thanks for your help!
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    In O1, copied across

    =SUMIF(O$3:O$100,"x",$S$3:$S$100)
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-11-2015
    Location
    Middle Earth
    MS-Off Ver
    MS2007
    Posts
    5

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    I think I may be doing something wrong. I put it in the first column (in this case M) and changed the O to M and S to Q to match the two columns in question, but it's coming up as a value of "0".

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    Leave the S as S... that's the column you're trying to add up.

  8. #8
    Registered User
    Join Date
    05-11-2015
    Location
    Middle Earth
    MS-Off Ver
    MS2007
    Posts
    5

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    Am I doing something wrong?
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,275

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    When I opened the file attached to post #8, Glen's formula had been pasted into M3 and copied down.
    Glen's instructions said to paste the formula into row one and copy across.
    Please follow these instructions to correct the file attached to post #8:
    1) Select cell M1,
    2) Delete the SUM formula currently shown in the formula bar
    3) Paste Glen's formula, =SUMIF(M$3:M$100,"x",$Q$3:$Q$100) into M1,
    4) Pull the fill handle for cell M1 across to cell O1,
    5) Select the range M3:M15 and press the delete key,
    6) Select the range N3:P3 and press the delete key,
    7) Select cell P4 and pull the fill handle up to cell P3.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Registered User
    Join Date
    05-11-2015
    Location
    Middle Earth
    MS-Off Ver
    MS2007
    Posts
    5

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    That was it! Thank you for explaining it. I apparently was having the case of the Monday and it didn't click. Thanks for all your help. This is exactly what I need.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,275

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: getting the value of the cell that has a forumla and avoid circular warnings

    Dohh!! Stoopid mistake by yours truly...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need formula to avoid circular references
    By Venkata Krishna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2014, 10:30 AM
  2. Replies: 2
    Last Post: 02-23-2014, 06:06 PM
  3. [SOLVED] How to avoid circular reference
    By CHoedeman in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 10:07 AM
  4. Technique to avoid circular reference
    By smjpl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2012, 10:34 AM
  5. IF AND forumla is giving me a circular ref error
    By controlfreak in forum Excel General
    Replies: 8
    Last Post: 10-05-2012, 05:52 AM
  6. Trying to Avoid a Circular Reference
    By dpmarkey in forum Excel General
    Replies: 1
    Last Post: 05-27-2010, 02:07 PM
  7. [SOLVED] Need to avoid a circular reference...
    By Denise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1