Solved

I need to update the following formula in Excel 2010

Posted on 2014-07-29
9
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

696 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