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

1. ## 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. ## Re: Function/Formula to return a value in worksheet 2 from worksheet 1 if criteria is met

Created DYNAMIC named range:
DemoPhase
Return unique phases to the BCC sheet column A:
A12:A38
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)

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

Thank you so much. This works perfectly

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

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