Need help in referencing

A

a580688

New Member
Joined
Sep 4, 2014
Messages
11
Hi All,

This is a bit tough for me so seeking out your help. I have the following data on sheet 3

Target DateCompletion DateStatus
May 23, 2014May 21, 2014Closed
May 16, 2014May 16, 2014Closed
June 13, 2014June 2, 2014Closed
June 13, 2014June 2, 2014Closed
May 20, 2014May 20, 2014Closed
July 8, 2014In Progress
May 30, 2014May 30, 2014Closed
August 15, 2014In Progress
August 15, 2014July 10, 2014Closed
September 15, 2014 In Progress

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I have at least 100 rows of this data. I want to show this data on another worksheet where it will pick up the date only where the Status is In Progress. It will not show me the other dates. I might have done this using a pivot table. But i want to reference this using a formula. Please help.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Welcome to the MrExcel board!

Here are two options. I have done it on a single sheet but hopefully you can adapt to two if it is what you want.

1. E2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

2. F2 does not require the Ctrl+Shift+Enter confirmation but does require Excel 2010 or later.

Excel Workbook
ABCDEF
1Target DateCompletion DateStatusTarget DateTarget Date
2May 23, 2014May 21, 2014ClosedJuly 8, 2014July 8, 2014
3May 16, 2014May 16, 2014ClosedAugust 15, 2014August 15, 2014
4June 13, 2014June 2, 2014ClosedSeptember 15, 2014September 15, 2014
5June 13, 2014June 2, 2014Closed
6May 20, 2014May 20, 2014Closed
7July 8, 2014In Progress
8May 30, 2014May 30, 2014Closed
9August 15, 2014In Progress
10August 15, 2014July 10, 2014Closed
11September 15, 2014In Progress
12
13
In Progress
 
Upvote 0
Thank You So Much Peter for such a quick reply. I am not very good with excel formula's. So i will give you the complete scenario. If possible please provide the complete formula.

Here are my Data details:

1. Target Date column is in Sheet 3 Column L.
2. Status column is in Sheet 3 Column N.
3. I want to show this data on Sheet 1.
4. Along with that beside the date's where Status = In Progress in the next cell I also want to show the status of the date.

Please provide me the complete excel formula.
 
Upvote 0
Excel Workbook
LMN
1Target DateCompletion DateStatus
2May 23, 2014May 21, 2014Closed
3May 16, 2014May 16, 2014Closed
4June 13, 2014June 2, 2014Closed
5June 13, 2014June 2, 2014Closed
6May 20, 2014May 20, 2014Closed
7July 8, 2014In Progress
8May 30, 2014May 30, 2014Closed
9August 15, 2014In Progress
10August 15, 2014July 10, 2014Closed
11September 15, 2014In Progress
12
Sheet 3



Each formula below is copied down.

Excel Workbook
ABCDE
1Target DateStatusTarget DateStatus
2July 8, 2014In ProgressJuly 8, 2014In Progress
3August 15, 2014In ProgressAugust 15, 2014In Progress
4September 15, 2014In ProgressSeptember 15, 2014In Progress
5
6
Sheet 1
 
Upvote 0
Thanks Peter.

Just Needed to know one last thing. Along with Status as In Progress. I might have few values with status as Overdue. What should be the formula to incorporate both In Progress and Overdue values.
 
Upvote 0
a580688 said:
Thanks Peter.

Just Needed to know one last thing. Along with Status as In Progress. I might have few values with status as Overdue. What should be the formula to incorporate both In Progress and Overdue values.
Click to expand...
Please try to give the complete problem, rather that gradually building up to it by adding extra requirement after extra requirement.

1. Does every row in Sheet 3 that has a date in column L have a value in the Status column?

2. If so, are there only 3 possible Status values: Closed, Overdue, In Progress?
 
Upvote 0
Sorry for the inconvenience Peter. I am new to this forum. So still trying to get used to it. :(

1. Every row in Sheet 3 that has a date in Column L has a value in the Status column.
2. There are three Status Values : Closed, In Progress, Overdue.
3. I want to show the dates in chronological order where the Status value is either In Progress or Overdue. I do not want to show the dates with Status Value as Closed.
4. In the first sheet it should show the respective status of the dates in the adjacent cell (either In Progress or Overdue)

----------------------------------------------------------------------------------------------------------------------------

I also have another similar issue. The data grid looks like this. This is in Sheet 2. :confused:

Target End DateCompleted DateStatus
May 14, 2014May 14, 2014Closed
May 22, 2014May 22, 2014Closed
June 4, 20142nd Draft
June 6, 2014June 6, 2014Closed
June 16, 2014June 16, 2014Final Draft
June 20, 2014June 11, 2014Closed
June 17, 2014June 17, 2014Final Draft
July 2, 2014July 2, 2014Closed
July 10, 20143rd Draft
July 21, 2014July 21, 2014Closed
July 24, 2014July 24, 2014Closed
August 5, 20141st Draft
August 14, 2014August 14, 2014Closed
August 19, 2014August 19, 2014Closed
August 20, 2014August 20, 2014Final Draft
September 3, 2014September 3, 2014Final Draft
August 29, 2014August 29, 2014Closed
September 8, 2014WIP

<tbody>
</tbody>

Target Date = Sheet 2 Column L
Status = Sheet 2 Column N

1. Every Row in Sheet 2 that has a Date in Column L has a value in the Status column.
2. There are Five Status Values : WIP,1st Draft,2nd Draft,3rd Draft, Final Draft,Closed
3. I want to show the dates in chronological order where the Status value is either WIP or 1st Draft or 2nd Draft or 3rd Draft. I do not want to show the dates with Status Value as Closed or Final Draft.
4. In the first sheet it should show the respective status of the dates in the adjacent cell (either WIP or 1st Draft or 2nd Draft or 3rd Draft)

----------------------------------------------------------------------------------------------------------------------------

I think this covers everything in details for both the requirements. Do let me know if you require any more details.

Many many thanks in advance.

Regards,
Arghya :biggrin:
 
Upvote 0
1. I have been posting two formula for each solution so far: One that works in all Excel versions but requires Ctrl+Shift+Enter entry and one that only works in Excel 2010 or later but does not require the C+S+E entry. You have not indicated what version you are using or what preference you have so I am now reverting just to the C+S+E type that will work in all versions of Excel.

2. This does not (yet) list the dates in chronological order as you have now requested, but let's see if otherwise this method is acceptable.

Excel Workbook
LMN
1Target DateCompletion DateStatus
2May 23, 2014May 21, 2014Closed
3May 16, 2014May 16, 2014Closed
4June 13, 2014Overdue
5June 13, 2014June 2, 2014Closed
6May 20, 2014May 20, 2014Closed
7July 8, 2014In Progress
8May 30, 2014Overdue
9August 15, 2014In Progress
10August 15, 2014July 10, 2014Closed
11September 15, 2014In Progress
12
Sheet 3



Excel Workbook
LMN
1Target End DateCompleted DateStatus
2May 14, 2014May 14, 2014Closed
3May 22, 2014May 22, 2014Closed
4June 4, 20142nd Draft
5June 6, 2014June 6, 2014Closed
6June 16, 2014June 16, 2014Final Draft
7June 20, 2014June 11, 2014Closed
8June 17, 2014June 17, 2014Final Draft
9July 2, 2014July 2, 2014Closed
10July 10, 20143rd Draft
11July 21, 2014July 21, 2014Closed
12July 24, 2014July 24, 2014Closed
13August 5, 20141st Draft
14August 14, 2014August 14, 2014Closed
15August 19, 2014August 19, 2014Closed
16August 20, 2014August 20, 2014Final Draft
17September 3, 2014September 3, 2014Final Draft
18August 29, 2014August 29, 2014Closed
19September 8, 2014WIP
20
Sheet 2



In Sheet 1, make list of the statuses you are interested in for each problem. I've used column D for problem 1 and column I for problem 2, but any columns could be used and these columns could be hidden if you want.
Each formula requires C+S+E entry and then is copied down as far as you might ever need.

Excel Workbook
ABCDEFGHI
1Target DateStatusRequired StatusTarget DateStatusRequired Status
2June 13, 2014OverdueIn ProgressJune 4, 20142nd DraftWIP
3July 8, 2014In ProgressOverdueJuly 10, 20143rd Draft1st Draft
4May 30, 2014OverdueAugust 5, 20141st Draft2nd Draft
5August 15, 2014In ProgressSeptember 8, 2014WIP3rd Draft
6September 15, 2014In Progress
7
Sheet 1
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Make a table from data which updated monthly.
Replies
1
Views
186
adnan1975
A
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
179
harzer
H
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
351
mrshuck
M
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
137
SunnyAlv
SunnyAlv
S
  • Question
Need Help in Dates
2 3
Replies
27
Views
930
Dave Patton
D

Forum statistics

Threads
1,214,897
Messages
6,122,141
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top