Please help - Linking worksheets

  • Thread starter Thread starter ceci
  • Start date Start date
C

ceci

I have a workbook with 2 worksheets. Sheet A is a data entry sheet (a large
excel list). Sheet B is a daily summary sheet that needs to be populated from
the info stored on Sheet A.

One of the columns in Sheet A contains various dates in no specific order.
What I need is for Sheet B to be updated dependent upon the date. If the
dates in the 'date column' of Sheet A match today's date it should drag the
other info from the row into Sheet B.

I have tried various combinations of IF statements in macros but I'm not too
hot at writing macros so can't make it do what I need it to.

Hope you can help! Let me know if this explanation is too short on detail to
be of use.
 
Assume dates (real dates) are running in A2 down in sheet: A,
and thatt the info to be extracted over lies in col B and C

In sheet: B
In A2:
=IF(A!A2="","",IF(A!A2=TODAY(),ROW(),""))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(A!B:B,SMALL($A:$A,ROWS($1:1))))
Copy B2 to C2. Select A2:C2, copy down to cover the max expected extent of
data in "A", eg down to C200? Cols B and C will return the required result
lines from "A" all neatly bunched at the top (viz those lines where the date
in col A is equal to TODAY ie the current date).

At each day-end, the process should be to make a frozen copy of "B", and to
label the copy unambiguously with the current date, eg label it as: 11Mar2008.
 
Thank you so much however, I have another question.

Assume the dates running down column A in sheet A (Master sheet) are random.
I have 12 other worksheets each named by Months. I need the information which
for example has the date from sheet A in January in the "January" worksheet,
and records in February in the "February" worksheet and so on.

Please help becuase I've been trying to figure this out for a couple of
weeks now and have no idea what i'm doing.

Ceci
 
.. I have 12 other worksheets each named by Months.
It's better to be unambiguous and name the 12 "child" sheets as: Jan08,
Feb08, etc (with the month/yr, rather than just the month)

Ok, here's one play which delivers what you're after. It auto-copies lines
from a master "parent" sheet by the date (key col) into the respective
month/yr "child" sheets using non-array formulas. In each mth/yr child sheet,
lines will be neatly bunched at the top and will also appear sorted in
ascending order by the date (additional bonus!)

The working set-up is illustrated in this sample:
http://www.freefilehosting.net/download/3db6c
AutoCopy Lines to Resp Sht Non Array_Dates.xls

In sheet: A (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (Dates)

List the 12 "child" sheetnames in K1 across:
Jan08, Feb08, Mar08, etc (can be in any order)
(do note that the sheetnames are entered as **text** with a preceding
apostrophe)

Put in K2:
=IF($A2="","",IF(TEXT($A2,"mmmyy")=K$1,$A2+ROW()/10^10,""))
Copy K2 across as far as required, then fill down to cover the max expected
extent of source data

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named: Jan08
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(A!A:A,MATCH(SMALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any month/yr. Here, I've assumed that 9
rows (rows 2 to 10) is sufficient)

Cols A to C will return only the lines for Jan08 from "WS1",
with all lines neatly bunched at the top and sorted in ascending order by date

Now, just make a copy of Jan08, rename it as the next one: Feb08, and we'd
get the results for Feb08. Repeat the copy > rename sheet process to get the
rest of the 12 mth/yr sheets (a one-time job). Adapt to suit ..

---
 
Thank you so much, this was exactly what I was looking for.

Max said:
.. I have 12 other worksheets each named by Months.
Click to expand...
It's better to be unambiguous and name the 12 "child" sheets as: Jan08,
Feb08, etc (with the month/yr, rather than just the month)

Ok, here's one play which delivers what you're after. It auto-copies lines
from a master "parent" sheet by the date (key col) into the respective
month/yr "child" sheets using non-array formulas. In each mth/yr child sheet,
lines will be neatly bunched at the top and will also appear sorted in
ascending order by the date (additional bonus!)

The working set-up is illustrated in this sample:
http://www.freefilehosting.net/download/3db6c
AutoCopy Lines to Resp Sht Non Array_Dates.xls

In sheet: A (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (Dates)

List the 12 "child" sheetnames in K1 across:
Jan08, Feb08, Mar08, etc (can be in any order)
(do note that the sheetnames are entered as **text** with a preceding
apostrophe)

Put in K2:
=IF($A2="","",IF(TEXT($A2,"mmmyy")=K$1,$A2+ROW()/10^10,""))
Copy K2 across as far as required, then fill down to cover the max expected
extent of source data

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named: Jan08
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(A!A:A,MATCH(SMALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any month/yr. Here, I've assumed that 9
rows (rows 2 to 10) is sufficient)

Cols A to C will return only the lines for Jan08 from "WS1",
with all lines neatly bunched at the top and sorted in ascending order by date

Now, just make a copy of Jan08, rename it as the next one: Feb08, and we'd
get the results for Feb08. Repeat the copy > rename sheet process to get the
rest of the 12 mth/yr sheets (a one-time job). Adapt to suit ..

---
ceci said:
Thank you so much however, I have another question.

Assume the dates running down column A in sheet A (Master sheet) are random.
I have 12 other worksheets each named by Months. I need the information which
for example has the date from sheet A in January in the "January" worksheet,
and records in February in the "February" worksheet and so on.

Please help because I've been trying to figure this out for a couple of
weeks now and have no idea what i'm doing.

Ceci
Click to expand...
Click to expand...
 
Back
Top