Hi Shelly, Here is your solution, assuming Last Month Formula : =VLOOKUP($G20,'[ABC ltd billing data - Jan''09 Final.xls]Pivot-ST'!$B$5:$I$398,8,0) New Formula you need : =VLOOKUP($G20,'[ABC ltd billing data - Feb''09 Final.xls]Pivot-ST'!$B$5:$I$398,8,0)
Solution: 1. Select all the data by pressing Ctrl+A. 2. Now pressCtrl+H to replace the filename in formulas. 3. Click Options to select "Look in" as Formulas. 4. In "Find what" box, please provide the last month file name. i.e.ABC ltd billing data - Jan''09 Final.xls 5. In "Replace with" box, please provide the this month file name. i.e.ABC ltd billing data - Feb''09 Final.xls 6. Press Ok. 7. A message box will appear showing how many replacements were made. It should be 250. For more tips, tricks, downloads and problem solutions: visit --> http://www.excelitems.com ------------------------------------------------------------------------ Regards *Ashish **Jain* Analyst, CSC Australia Microsoft Certified Application (Excel) Specialist Technical Trainer - Excel and VBA Author -> www.excelitems.com +91-9999-40-48-43 ------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- On Apr 16, 1:40 pm, 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
