+ Reply to Thread
Results 1 to 2 of 2

Copy formula from one sheet to anotehr and change the corespening sheet name inthe formula

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Copy formula from one sheet to anotehr and change the corespening sheet name inthe formula

    Hi, I am new to the form but other member questions has helped me alot in the past - Thanks

    I have a problem that is a bit difficult for ne to explain but Iíll try

    I have a Master workbook that updates from 3 other workbooks. Each workbook has a master sheet and then 30 worksheets. My problem is I want to copy formulas from one worksheet to another worksheet in my master and change the corresponding reference worksheet.

    Example:
    How do I copy the formula into Sheet3 A1 of the Master Workbook and have the corresponding sheet updated in the formula (should read: '[Workbook1]Sheet3'!A1+'[Workbook 2]Sheet3'!A1+'[Workbook 3]Sheet3'!A1) If I copy the formal from sheet1 the formula stays the same (Workbook1]Sheet1'!A1+'[Workbook 2]Sheet1'!A1+'[Workbook 3]Sheet1'!A1)

    Master Workbook
    Sheet1 A1= '[Workbook1]Sheet1'!A1+'[Workbook 2]Sheet1'!A1+'[Workbook 3]Sheet1'!A1
    Sheet2 A1 should be = '[Workbook1]Sheet2'!A1+'[Workbook 2]Sheet2'!A1+'[Workbook 3]Sheet2'!A1
    Sheet3 A1=

    Workbook 1
    Sheet1
    Sheet2
    Sheet3

    Workbook 2
    Sheet1
    Sheet2
    Sheet3

    Workbook 3
    Sheet1
    Sheet2
    Sheet3

    Any ideas?

    Thanks

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copy formula from one sheet to anotehr and change the corespening sheet name inthe for

    Copy the formulas enmasse to the new sheet/positions. Then highlight the formula cells and press CTRL-H to use the REPLACE function to edit all the formulas at once.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1