Solved

# I need to update the following formula in Excel 2010

Posted on 2014-07-29
185 Views
I am trying to update a formula that I got working with some help from Exp Exchange yesterday. I am not sure if we can do it, but I wanted to give it a try. See the attached example.

I need to update the following formula:

=IF(\$G200="M",IFERROR(INDEX('[Report 7-29-14.xlsx]Projects'!\$G\$10:\$G\$150,MATCH(\$A10,'[Report 7-29-14.xlsx]Projects'!\$A\$10:\$A\$150,0))&"",""),IF(I10="","",TEXT(I10,"M/D/YY")&" (E)"))

The formula returns values in Column G.

I need it to, after it returns values, it looks at Column V. If the info in Column V is the same, V12=V13=V14, it looks back at Column G, finds the earliest date, 6/21/15 (E), and returns that date for the other matching cells.

I know this is confusing. I am trying to return the earliest date in Column G for all of the matches in Column V.
EXP-EXCH-7-29-14.xlsx
0
Question by:wrt1mea
• 4
• 4

LVL 18

Assisted Solution

krishnakrkc earned 100 total points
ID: 40227005
Hi

Try this array formula.

=IF(COUNTIF(\$V\$10:V10,V10)=1,MIN(IF(\$V\$10:\$V\$40=V10,LEFT(\$G\$10:\$G\$40,FIND(" ",\$G\$10:\$G\$40&" ")-1)+0)),"")

Kris
0

LVL 27

Expert Comment

ID: 40227101
This is confusing, because your example formula appears to be self-referential, that is, it is checking values IN column G for the occurrence of "M", but the values you have in column G now look like the results of this formula.

And while Kris' array formula (entered with [Ctrl]+[Shift]+[Enter) is absolutely correct in returning the earliest (minimum) date in column G for any given value in column V, you don't specify where this formula needs to go or if it is somehow supposed to be incorporated into the example formula.

Please clarify these issues and I'm sure we'll get an acceptable answer to you.

Regards,
-Glenn
0

LVL 1

Author Comment

ID: 40227368
OK. Here is what I am trying to do. Maybe I need to reference a different column....

The formula that I am using to return dates in Column G works perfectly in my production sheet. If it finds an M, it returns a previous weeks report date information, if it does not, it returns a new date based on the last part of the formula. I know you probably know how to read a formula, just was saying.

I would love for the results after going through my first formula, go through the second formula that Kris posted and display the results in Column G. So yes, incorporating the formula that Kris posted into my formula would be ideal. Like I mentioned previously, if I need to reference another column, Like Column AZ to return info first, then run the formula in column G to display the new results, I am fine with that.

Hope this helps
0

LVL 27

Expert Comment

ID: 40227439
So, am I correct in stating that you want the formula now to display the earliest completion date in column G for any given area name?   See this:
0

LVL 1

Author Comment

ID: 40227446
YES!!! Looks good!
0

LVL 1

Author Comment

ID: 40227466
But I am wanting the formula to display the earliest completion date, after the original formula I posted runs and evaluates the data to return.
0

LVL 27

Accepted Solution

Glenn Ray earned 400 total points
ID: 40227563
I presumed that you wanted the original algorithm (formula) to return a date - either from the external Projects sheet, or from column I - but then only display the earliest for any given Area Name.

The only way to do this is by converting the existing Completion Date formulas into a "helper" column and displaying a new Completion Date column.  This would essentially make Kris' solution the new displayed result (you could hide/move the existing set of formulas).

I've created an example and have modified Kris' formula somewhat to always show the earliest completion date (not just at the start of each Area Name group).  The original formulas are in column AA, but could be anywhere, even on another sheet.

Regards,
-Glenn
EE-EXP-EXCH-7-29-14.xlsx
0

LVL 1

Author Closing Comment

ID: 40227707
AWESOME!!!!!!!!!!!!!!!

THANK YOU VERY MUCH! GONNA SAVE A TREMENDOUS AMOUNT OF TIME!
0

LVL 27

Expert Comment

ID: 40227711
YEA!  I'm glad we could help.

-Glenn
0

## Featured Post

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.