Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel workbooks
The following is what I have to work with I have 12 months of data save in
twelve spread sheets for approximately 1000 funds. I need to copy eight columns from each work sheet into a linked workbooks which have twelve worksheets each month uses the month end date for example 2007-01-31 for January. I need to give information in for the 1000 funds for the entire year I made a template which I will be using and I am going to copy and paste this information from one workbook to another because the original workbook has information that I do not need. How can I set a formula in the event I copy the incorrect data in the workbook my files are largesince I am using month end as my initial step to copy the information. See information example below of what I am going to copy and paste. Account & branch Date Account number Branch depreciation 1010500 2007/01/31 1040 500 10 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel workbooks
Think you could try this in a spare copy of your linked workbook
In your linked workbook, Gather that your 12 monthly sheets are named as: 2007-01-31 2007-02-28 2007-03-31 etc First, create a defined range: WSN 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. What it means is we can place in any cell, in any sheet, eg: =WSN, and that cell will return the sheetname (workbook is presumed saved earlier). Eg, in the "January" sheet named as: 2007-01-31, it'll return: 2007-01-31 (as text) Assume the 8 cols of pasted data will be in done cols A to H in all 12 monthly sheets, data from row2 down, where col B = dates (real dates are presumed) Select all the 12 monthly sheets (Group the sheets) Then in an empty adjacent col to the right of the pasted data, say in col I, copy n paste this formula into the formula bar for I2: =IF(LEN(TRIM(B2))=0,"",TEXT(DATE(YEAR(B2),MONTH(B2 )+1,0),"yyyy-mm-dd")=WSN) Copy I2 down to cover the max expected extent of data for any one particular month, eg down to I1000. Then right-click on any grouped tab Ungroup Sheets (remember to do this). Col I will help you to identify whether there's any incorrect lines pasted. It'll return TRUE for correctly pasted lines (ie if dates in col B fall within the month/yr indicated by the sheetname), FALSE otherwise, or "" if col B is blank. Just do an autofilter on col I, filter/check for FALSE (if any) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Buxton" wrote: The following is what I have to work with I have 12 months of data save in twelve spread sheets for approximately 1000 funds. I need to copy eight columns from each work sheet into a linked workbooks which have twelve worksheets each month uses the month end date for example 2007-01-31 for January. I need to give information in for the 1000 funds for the entire year I made a template which I will be using and I am going to copy and paste this information from one workbook to another because the original workbook has information that I do not need. How can I set a formula in the event I copy the incorrect data in the workbook my files are largesince I am using month end as my initial step to copy the information. See information example below of what I am going to copy and paste. Account & branch Date Account number Branch depreciation 1010500 2007/01/31 1040 500 10 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Display 2 formulas from source workbooks to destination workbooks | Excel Discussion (Misc queries) | |||
suddenly my excel workbooks are "shared workbooks" | Excel Discussion (Misc queries) |