+ Reply to Thread
Results 1 to 4 of 4

Copying formula but replacing the reference (using specific criteria not automatic)

  1. #1
    Registered User
    Join Date
    04-01-2021
    Location
    Houston, US
    MS-Off Ver
    365
    Posts
    2

    Copying formula but replacing the reference (using specific criteria not automatic)

    Good morning,

    Thanks for all the feedback you provide, this forum is very helpful. I have a table with multiple columns, each column is counting the yeses from another worksheet and there's one column for each sheet. I need to copy the formulas from one column to another column, the formula should be exactly the same with the exception of the name of the reference that is pulling the info from. Please see example below:

    Copy this =COUNTIF(tree!C7:C22, "Yes")
    And paste it as =COUNTIF(panda!C7:C22, "Yes")

    I have Excel 365. Is there a way of doing that without actually going and manually replacing tree with panda? The column has about 200 rows so that would be a lot of work!

    I'll really appreciate your assistance with this.

    Thank you!

    Andrea
    Last edited by an868712; 04-01-2021 at 10:22 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,685

    Re: Question about copying formula from one column to another but replacing the reference

    if you type in
    =COUNTIF(panda!$C$7:$C$22, "Yes")
    in the first row and provide there is data in the column before - move the curser to the bottom right corner - should turn into a black cross and double click it will populate all the rows with the same formula all the way down
    OR
    same position and Drag the formula down 200 rows
    No Need to edit each one
    OR
    copy the formula to the new column completely
    and then
    using the replace function in the menu FIND/REPLACE
    change TREE to PANDA
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-01-2021
    Location
    Houston, US
    MS-Off Ver
    365
    Posts
    2

    Re: Copying formula but replacing the reference (using specific criteria not automatic)

    This is perfect. Thank you Wayne!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,685

    Re: Copying formula but replacing the reference (using specific criteria not automatic)

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Replacing sheet number in a formula by cell reference
    By ajeshkdy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2019, 07:58 AM
  2. [SOLVED] Replacing formula with $ variable cell reference
    By PQK78 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-17-2015, 12:14 AM
  3. Auto Replacing Absolute Reference Cells a Column of Data to Obtain Output
    By kambing007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2015, 08:02 AM
  4. Replies: 1
    Last Post: 02-02-2014, 03:37 PM
  5. [SOLVED] Excel 2007 : Change Column Reference when Copying Down
    By HangMan in forum Excel General
    Replies: 3
    Last Post: 04-12-2012, 07:18 AM
  6. Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  7. copying a column reference into a row
    By lars1028 in forum Excel General
    Replies: 4
    Last Post: 06-26-2006, 05:20 PM

Tags for this Thread

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