Solved

I need to update the following formula in Excel 2010

Posted on 2014-07-29
9
185 Views
Last Modified: 2014-07-29
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
Comment
Question by:wrt1mea
  • 4
  • 4
9 Comments
 
LVL 18

Assisted Solution

by:krishnakrkc
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

by:Glenn Ray
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

by:wrt1mea
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

by:Glenn Ray
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:
example update
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:wrt1mea
ID: 40227446
YES!!! Looks good!
0
 
LVL 1

Author Comment

by:wrt1mea
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

by:
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

by:wrt1mea
ID: 40227707
AWESOME!!!!!!!!!!!!!!!

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

Expert Comment

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

-Glenn
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now