+ Reply to Thread
Results 1 to 7 of 7

edit the excel formula with different ranges

Hybrid View

  1. 02-16-2015, 02:47 AM #1
    Masa1989
    Masa1989 is offline
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

    edit the excel formula with different ranges

    Hi Guys

    I am looking out for a way to edit the excel formula without changing the references

    Say i have formula in B5= (B1+B2-B3-B4), this formula is there in full B cloumn (With different formulas in between) and every B+7 column

    What i want is eidt the formula in B5= (B1+B2-B3+B4), change the operator to "+sign"

    I would just change it in cell b1 and would copy paste as formulas in column B, but column b has subtotals in between , i cant select the full range and copy paste it, and more over i have to replicate the same in other columns

    Is there any way can i change the formula structure from minus to + across full sheet, is VBA required for this??
    Reply With QuoteRegister To Reply
  2. 02-16-2015, 04:16 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,709

    Re: edit the excel formula with different ranges

    Try doing a Replace all with what: B3-B4 and with: B3+B4


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 02-16-2015, 06:10 AM #3
    Masa1989
    Masa1989 is offline
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

    Re: edit the excel formula with different ranges

    hey i am sorry i explained it wrong

    Actual formula is E5=(A5+B5-C5-D5)

    what i want is E5=(A5+B5-C5+D5)

    And across all the column, the ranges will be different, so i cant use find and replace option
    Reply With QuoteRegister To Reply
  4. 02-16-2015, 06:15 AM #4
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: edit the excel formula with different ranges

    I bet you get better help on your file, if you add an small excel file, without confidential information.

    Please also add the desired result in your sheet.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  5. 02-16-2015, 06:32 AM #5
    Masa1989
    Masa1989 is offline
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

    Re: edit the excel formula with different ranges

    hey i have attached the example sheet
    Hope it explains now
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-16-2015, 06:42 AM #6
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: edit the excel formula with different ranges

    Make a table of your data.

    excel 2007 => insert table

    Since you have gaps in your data, you have to manualy select the table.

    Then you only have to change the formula in row 7.

    The formula will automatic be expanded to the last cell (of that column) in the table.

    You could copy the formula to the next yellow cell.
    Reply With QuoteRegister To Reply
  7. 02-16-2015, 06:49 AM #7
    Masa1989
    Masa1989 is offline
    Forum Contributor
    Join Date
    12-11-2014
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    142

    Re: edit the excel formula with different ranges

    i have to individually select yellow clored column and change the formula in 1st cell u mean?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Allow users to edit ranges in excel 2000
    By margamboy in forum Excel General
    Replies: 1
    Last Post: 11-15-2013, 02:42 AM
  2. Protection and Edit Ranges
    By tedcahill2 in forum Excel General
    Replies: 1
    Last Post: 05-03-2012, 05:04 PM
  3. allow users to edit ranges
    By muralidaran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-06-2012, 05:33 PM
  4. Allowing Multiple Users to Edit Specified Ranges in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 10-31-2007, 08:35 AM
  5. allow users to edit ranges
    By neeraj in forum Excel General
    Replies: 2
    Last Post: 09-23-2005, 10:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1