Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking worksheets | Excel Discussion (Misc queries) | |||
linking worksheets | Excel Discussion (Misc queries) | |||
linking worksheets | Excel Worksheet Functions | |||
Linking worksheets | Links and Linking in Excel | |||
Linking worksheets | Links and Linking in Excel |