Please help - Linking worksheets
 

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.

Please help - Linking worksheets
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ceci" wrote:
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.

Please help - Linking worksheets
 

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

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ceci" wrote:
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.

Please help - Linking worksheets
 

.. 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(SM ALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROW S($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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ceci" wrote:
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

Please help - Linking worksheets
 

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

"Max" wrote:

.. 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(SM ALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROW S($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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ceci" wrote:
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

Please help - Linking worksheets
 

Great to hear that!
You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ceci" wrote in message
...
Thank you so much, this was exactly what I was looking for.