Shelly, You could try the following
use the formule: =VLOOKUP($B20,INDIRECT(Filename&"Pivot-ST'!$B$5:$I$398",8,0) create a cel named "Filename" and put in here the the name of the workbook. ''[ABC ltd billing data - Jan''09 Final.xls]' (note the dubbel '' in the text) For the next month you only have to change the filename in one cell. Succes, Frans On Apr 16, 10:40 am, Shelly Mehta <shellyme...@gmail.com> wrote: > Dear All, > > Pls. help/ advice on the following query: > > *Background: * > > I have to prepare invoices (approx 250) every month (eg. Month of Jan) which > I link with the Pivot sheet as a supporting for each month. I have made one > sheet (attached) which I copy and move to another file every month (eq. Feb > pivot details). > > *Query:* > > But in that case I always have to change the formula marked in yellow (in > the sheet attached) that means approx 250 times. > Is there any easy way out to replace the formula rather than making changes > 250 times. Pls. suggest in case if there is a easy way out apart from the > one i m following. > > Thanks in anticipation. > > Regards, > > Shelly Mehta > > book invoice.xls > 189KViewDownload --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
