+ Reply to Thread
Results 1 to 9 of 9

Cell value dependent on values of multiple cells

  1. #1
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Cell value dependent on values of multiple cells

    Hi not sure if this is even possible...

    I have cells E3-E13 which all have data validation lists, to label if a purchase order on a job has been invoiced or not. The options in these cells are "No", "Partly" or "Fully", the cell could also be blank if not selected/ required.

    In cell I wanted a formula to return a text value based on the contents of cells E3-E13 to give an overall status of the job.

    If all cells are blank, E3 must be blank

    If the cells contain "No" or blanks, E3 must say "Not Invoiced"

    If any cells say "Partly invoiced", E3 must say "Partly invoiced" even if some of the other cells in that range say "No" or "Fully"

    E3 can only change to "Fully invoiced" once all cells have been updated to either "Fully" or are blanks.

    Hope that makes sense, any ideas much appreciated.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Cell value dependent on values of multiple cells

    No. This one is needs a sample sheet. See my comment on your other active thread...



    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: Cell value dependent on values of multiple cells

    Quote Originally Posted by Glenn Kennedy View Post
    No. This one is needs a sample sheet. See my comment on your other active thread...



    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (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.
    Hi, only been back on here 5 mins and getting myself in trouble already... Apologies again - This is the same workbook as i have just uploaded on my other post, but attached again as requested. Sorry again for my bad etiquette.
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Cell value dependent on values of multiple cells

    1. remove data validation from E3, E14, etc.

    2. Use this in E3:
    =IF(COUNTBLANK(E4:E13)=10,"",IF(COUNTIF(E4:E13,"Partly")>0,"Partly Invoiced",IF(COUNTIF(E4:E13,"No")>0,"Not Invoiced","Fully Invoiced")))

    3. Select all cells in range E3:E13.

    4. Drag down as far as needed.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: Cell value dependent on values of multiple cells

    HI Glenn, thanks for the reply much appreciated! Have been away so apologies for delayed response.

    This almost works perfectly, except -

    - Its actually B12 that needs to have the formula written in, completely my error in description, I can update this no problem.

    - If E3-E13 has only 'No's and then one or more of the cells is changed to 'Fully' B12 needs to change the text value to 'Partly Invoiced' currently it stays as 'Not invoiced' until a cell is changed to 'Partly'

    - I intend on copying the table in range A3:I13 many many times underneath, maybe 1000 plus rows, so i need the formula in B12 to copy to B23, B33 etc but be relative to the Cells in the relevant E column for that table (hope that makes sense) I was given this formula for a different function but which is needed for the same reason, but i am not sure how i combine the 2?

    =IF((OR(INDEX($B$6:$B$1000,1+11*INT((ROWS($1:4)-1)/11))="Onsite",INDEX($B$6:$B$1000,1+11*INT((ROWS($1:4)-1)/11))="Complete")),G6,"")

    Any assistance greatly appreciated.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Cell value dependent on values of multiple cells

    I think you got that one from me!!

    Apart from B10, B11 and (now) B12, are there ANY cells in the range B3 to B13 that will contain formulae. If not, use this in B12:

    =IF(COUNTBLANK(E3:E13)=11,"",IF(COUNTIF(E3:E13,"Partly")>0,"Partly Invoiced",IF(COUNTIF(E3:E13,"No")>0,"Not Invoiced","Fully Invoiced")))

    Select B3:B13. With all cells selected, drag down as far as needed.

    see sheet.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    09-04-2013
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    117

    Re: Cell value dependent on values of multiple cells

    Quote Originally Posted by Glenn Kennedy View Post
    I think you got that one from me!!
    That would be why it wouldn't let me give you rep for your reply above then :-) Thanks again for that one!

    Perfect thanks for the reply on copying down the formulas!

    The formula still does not display 'Partly invoiced' when needed - See sample sheet for 2 scenarios where it should say this.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Cell value dependent on values of multiple cells

    Away for the night. I'll fix it in a.m... assuming no-one else has.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Cell value dependent on values of multiple cells

    OK. Here you go:

    =IF(COUNTBLANK(E3:E13)=11,"",IF(COUNTIF(E3:E13,"Partly")>0,"Partly Invoiced",IF(AND(COUNTIF(E3:E13,"No")>0,COUNTIF(E3:E13,"Fully")>0),"Partly Invoiced",IF(COUNTIF(E3:E13,"No")>0,"Not Invoiced","Fully Invoiced"))))

    File attached.

    Regarding rep... yes you can't give it to the same person twice (or more...) in a row. Someone else has to benefit in-between times. It's designed to stop private arrangements....
    Attached Files Attached Files

+ 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. Conditional Formatting Dependent on Values of Two Cells
    By eemiller1997 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-03-2017, 11:41 AM
  2. [SOLVED] Finding multiple max values that are dependent on a value of another column
    By dennis.pak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2016, 04:24 AM
  3. multiple values dependent on their own sum (circular reference)
    By Philipp O. in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-16-2015, 03:50 PM
  4. Replies: 2
    Last Post: 06-23-2014, 10:40 PM
  5. Sum values from multiple cells - multiple lookup values in single cell
    By taxdept in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 04:12 PM
  6. [SOLVED] 2nd value in cell range dependent on changing values other cells (dynamic?)
    By RUJedi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2012, 07:34 PM
  7. Cell value dependent on the values of other Cells.
    By FShaw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2009, 01:40 PM

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