Excel workbooks

[Buxton]

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

 

[Max]

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)

---