Formulas to reference range based on data in column

  • Thread starter Thread starter Code Numpty
  • Start date Start date
C

Code Numpty

I probably didn't title this too well.

I have a spreadsheet with 500 rows of data, sorted by the date in column A.
I want to extract certain data from rows that all start with the same
specified date.

Previously I used an IF formula but that necessitates having the formula in
500 rows which won't work for my purposes.

I'm going round incircles trying to think how best to achieve this.
 
If you apply a filter to the date column for the selected date you can
then just highlight the visible rows and copy/paste or cut/paste them
elsewhere (eg another sheet) to extract them.

Hope this helps.

Pete
 
I knew I didn't word it well.

Information is often being added or changed in the original sheet but it is
always sorted back into date order on save.

What I need is for the extracted data to appear automatically in the other
sheet and always be up to date.
 
Well, give us some details. What data do you have in your original
sheet? Where are the dates, and do these increase each day? Do you
have several entries for each date? What exactly do you want to
extract? etc. etc.

Pete
 
Original sheet contains dates, names, numbers and notes. Dates often change
and new dates are added. Older dates are regularly deleted. Sheets are
automatically sorted by date on save.

Example Data (with header row, column letters in brackets)

In (A) day (B) Out (C) day (D) Pen (E) Cat/s (F) Last Name (G) Tel No
(H) Arrival Notes (I) Collection Notes (J) Days (K) Rate (L) Deposit
(M) Total £ (N)
15/3/10 Mon 1/4/10 Thu 25 Flossy Nelson 012345 6793 18 £6.15 110.70
18/3/10 Thu 3/4/10 Sat 11 Izzy Prosser 012345 6794 17 £6.15 104.55
1/4/10 Thu 6/4/10 Tue 4 Jasper Davies 012345 6795 6 £6.15 36.90
1/4/10 Thu 6/4/10 Tue 6 Dusty & Smudge Catchpole 012345 6790 6 £9.50 57.00
1/4/10 Thu 6/4/10 Tue 22 Batman & Robin King 012345 6792 6 £9.50 57.00
1/4/10 Thu 6/4/10 Tue 26 Muffin Tristram 012345 6793 New Customer, sign
Card PAID 6 £6.15 24.60 12.30
3/4/10 Sat 6/4/10 Tue 21 Smudge & Ginger Smith 012345 6791 4 £9.50 38.00


In another workbook I want to pull in certain data based on the date in
column A.

In one section e.g. (all based on the same date in column A)

Pen (E) Cat/s (F) Last Name (G) Out (C) day (D) Arrival Notes (I)

Example data

Pen (E) Cat/s (F) Last Name (G) Out (C) day (D) Arrival Notes (I)
4 Jasper Davies 6/4/10 Tue
6 Dusty & Smudge Catchpole 6/4/10 Tue
22 Batman & Robin King 6/4/10 Tue
26 Muffin Tristram 6/4/10 Tue New Customer, sign Card

I want this workbook to update so that the data is always current, taking
into account changed dates and dates that have been added. I will always be
looking for data for a single date only.

I hope this is a little clearer.

Sharon
 
Okay, Sharon, it looks like column O is free in your data sheet, so
put "Seq" in O1 and this formula in O2:

=IF(A2="","-",A2&"_"&COUNTIF(A$1:A2,A2))

and copy this down beyond your data. It will give a sequential number
after the date serial number.

Then assuming you put your date of interest in H1 of the second sheet,
then in H2 you can put this formula:

=IF(ISNA(MATCH(H$1&"_"&ROW(A1),data!O:O,0)),"",MATCH(H
$1&"_"&ROW(A1),data!O:O,0))

Copy this down as far as you think you will need it (i.e. to cover the
maximum number of entries that you are likely to have on any one day,
eg to row 20). Then you can put your headings in A1: F1, and you can
have a formula like this in A2:

=IF($H2="","",INDEX(data(E:E),$H2))

This will bring the matching data from column E of the data sheet. A
similar formula in B2:

=IF($H2="","",INDEX(data(F:F),$H2))

will bring data from column F of the data sheet. Just set up similar
formulae in C2:F2 to suit the data you want to bring across, changing
the F:F to the appropriate column. Format these cells as appropriate
(eg dates), then copy the formulae down to row 20.

Now, you can just change the date in H1 to see the data change.

If you delete or add any rows in the data sheet, you will need to copy
the formula in column O to ensure it covers the data that you have.

Hope this helps.

Pete
 
You may also consider using the Pivot Table tool if you have Excel 2007.
Sorting and analyzing data with this tool is a whiz.

e-Learning
 
Pete, it works a treat!

My project was a lot more complicated than I let on but I have managed to
adapt what you gave me and actually use it in 2 separate places to achieve a
single sheet that gives me all bookings in and all bookings out on a single
date that will always be current. It has also helped me to get a better
understanding of how I can use INDEX and MATCH.

You've been a great help.

Sharon
 
Back
Top