Extracting rows with a specific date

[LisetteR]

Hi, I have used Excel Magic Trick 185 to copy data from one list to another in a different worksheet. It comes up with a circular reference and I wondered if anyone could tell me what I'm doing wrong.

=IF(ROWS(A$32:A32)<=$K$3,INDEX(INDIRECT($A$31),SMALL(IF(Date=$I$3,ROW(Date)-ROW(DailyProgressLog!$A$14)+1),ROWS(A$32:A32))),"")

Cell A31 on the second worksheet is the Title "Date". K3 counts the number of occurences and is working. When I break the formula into parts the Small function box produces the right number (I think!) but it only shows 0 in the cell when I press enter.

I can send/attach jpgs if anyone is interested in seeing them for more info.

Many thanks in advance.

Lisette

 

[T. Valko]

Hi, I have used Excel Magic Trick 185 to copy data from one list to another in a different worksheet. It comes up with a circular reference and I wondered if anyone could tell me what I'm doing wrong.

=IF(ROWS(A$32:A32)<=$K$3,INDEX(INDIRECT($A$31),SMALL(IF(Date=$I$3,ROW(Date)-ROW(DailyProgressLog!$A$14)+1),ROWS(A$32:A32))),"")

Cell A31 on the second worksheet is the Title "Date". K3 counts the number of occurences and is working. When I break the formula into parts the Small function box produces the right number (I think!) but it only shows 0 in the cell when I press enter.

I can send/attach jpgs if anyone is interested in seeing them for more info.

Many thanks in advance.

Lisette

Why would you use INDIRECT to refer to Date but then at the same time use SMALL(IF(Date ?

There's a good example of how to do this here:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[LisetteR]

Hi,

Thank you for your reply.

I have tried the suggestion and I am getting the same result, it seems that it is not recognising the I3 date cell, or the correct row selection, although it is putting in the right number of rows.

This is the new code:
=IF(ROWS(A$32:A32)>$K$3,"",INDEX(Date,SMALL(IF(Date=$I$3,ROW(Date)),ROWS(A$32:A32))))

My data table has defined headers at row 13 if that makes a difference.

Thanks,

Lisette

 

[T. Valko]

Hi,

Thank you for your reply.

I have tried the suggestion and I am getting the same result, it seems that it is not recognising the I3 date cell, or the correct row selection, although it is putting in the right number of rows.

This is the new code:
=IF(ROWS(A$32:A32)>$K$3,"",INDEX(Date,SMALL(IF(Date=$I$3,ROW(Date)),ROWS(A$32:A32))))

My data table has defined headers at row 13 if that makes a difference.

Thanks,

Lisette

Ok, it looks like you simply want to list the dates if they are the same as the date entered in I3. Is that correct?

Is there more to this or is that all you're wanting to do?

 

[LisetteR]

Hi yes that's exactly it.

I have a DailyProgressLog sheet where we will record:
A13 Date
B13 Start time
C13 End Time
D13 Desciption (of Activity)
E13 Activity Code
F13 ActivityTime (Calculates time spent on activity from Start and End Time)

Then in the DailyProgressReport sheet I have a daily report with a space to show the activities (with the above column headings) for the date specified in cell I3.

Thanks,

Lisette

 

[T. Valko]

Hi yes that's exactly it.

I have a DailyProgressLog sheet where we will record:
A13 Date
B13 Start time
C13 End Time
D13 Desciption (of Activity)
E13 Activity Code
F13 ActivityTime (Calculates time spent on activity from Start and End Time)

Then in the DailyProgressReport sheet I have a daily report with a space to show the activities (with the above column headings) for the date specified in cell I3.

Thanks,

Lisette

Ok, if you only need to extract one row of data based on a date you could use a much less complicated VLOOKUP formula.

See if this is what you had in mind.

Let's assume this is your Daily Progress Log...

Book1
	A	B	C	D	E	F
12	Date	Start	End	Description	Code	Time
13	5/1/2011	7:00 AM	10:25 AM	X	10	5
14	5/2/2011	8:15 AM	9:40 AM	Y	30	10
15	5/3/2011	7:50 AM	11:35 AM	Z	20	7
16	5/4/2011	9:55 AM	10:20 AM	X	10	21
17	5/5/2011	2:10 PM	4:00 PM	Z	10	10
18	5/6/2011	1:20 PM	5:10 PM	Y	20	20
Sheet1

And this is your summary area...

Book1
	A	B	C	D	E	F
1	Look up	_	_	_	_	_
2	Date	Start	End	Description	Code	Time
3	5/3/2011	7:50 AM	11:35 AM	Z	20	7
Sheet1

Enter this formula in B3 and copy across to F3:

=VLOOKUP($A3,$A$13:$F$18,COLUMNS($A2:B2),0)

 

[LisetteR]

Hi, I am really appreciating your help here!

There are more than 1 lines with the same date as we would complete different activities during the day.

Thanks,

Lisette

 

[T. Valko]

Hi, I am really appreciating your help here!

There are more than 1 lines with the same date as we would complete different activities during the day.

Thanks,

Lisette

Ok, I'll put together a sample file...

Give me a few minutes...

 

[T. Valko]

Ok, I'll put together a sample file...

Give me a few minutes...

Here's a small sample file that demonstrates this.

The array formula entered on Sheet1 in D3 is copied across to I3 then down until you get blanks. You have to copy the formula to enough rows to ensure you get all the records of interest. How many rows will that be? Only you know the answer to that!

zzzLisetteR.xls 23kb

http://cjoint.com/?AErrzz6b1em

 

[LisetteR]

Hi, just wanted to say thankyou.

I'm still having trouble with this as it doesn't seem to pick the right row and it doesn't work on the rows below.

I will be back later but need to park this for a while as have other work!

Many thanks, Lisette