+ Reply to Thread
Results 1 to 3 of 3

Function/Formula to return a value in worksheet 2 from worksheet 1 if criteria is met

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Adelaide, South Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Function/Formula to return a value in worksheet 2 from worksheet 1 if criteria is met

    I have two worksheets.

    The first worksheet (COST ESTIMATE) contains one column with drop down lists used to select items called "phase".

    The selection of the Phase is then coupled with various other elements in that row and a $ value is calculated, i.e. the cost of that phase of the project

    In the second worksheet (BUILDING CUSTOMER COPY) I have a "sumif" formula that totals the value of the calculations used against each Phase.

    I would like to use a formula in the rows of column A of the BUILDING CUSTOMER COPY worksheet that looks in column A of COST ESTIMATE and returns the string value of that phase. In some cases the phase is replicated on multiple rows. I just want to pull one occurrence of the phase into worksheet 2. Then the sumif will do its thing and sum the value of the phase if is used more than once in worksheet 1.

    Then to keep looking for unique "phase" values in COST ESTIMATE until it has found all and return them only once in the BUILDING CUSTOMER worksheet.

    The sumif function in column M of the BUILDING CUSTOMER COPY will calculate the total value of the phase.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    8,891

    Re: Function/Formula to return a value in worksheet 2 from worksheet 1 if criteria is met

    Created DYNAMIC named range:
    DemoPhase
    Please Login or Register  to view this content.
    Return unique phases to the BCC sheet column A:
    A12:A38
    Please Login or Register  to view this content.
    I modified the named range below to make it dynamic to exclude blank cells:

    Project_Phase ='Rate Tables & Lists'!$H$12:INDEX('Rate Tables & Lists'!$H:$H,COUNTA('Rate Tables & Lists'!$H:$H)+8,1)
    ='COST ESTIMATE'!$A$10:INDEX('COST ESTIMATE'!$A:$A,COUNTA('COST ESTIMATE'!$A:$A)+3)
    Last edited by protonLeah; 09-09-2017 at 07:39 PM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-13-2012
    Location
    Adelaide, South Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Function/Formula to return a value in worksheet 2 from worksheet 1 if criteria is met

    Thank you so much. This works perfectly

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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