+ Reply to Thread
Results 1 to 6 of 6

Equation That Will Automatically Change Cell Data

  1. #1
    Forum Contributor garrett.grillo's Avatar
    Join Date
    06-19-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2010
    Posts
    188

    Equation That Will Automatically Change Cell Data

    I need help on an equation in the (Automated Data Correction) sheet that will copy over all the information from the (Raw Data Export) sheet, but will as well, based on the “Name” column (B), access specified changes that have been manually made to each cell contained in the (Manual Data Correction) sheet and based on those specific manual changes, the equations in the (Automated Data Correction) sheet will automatically make those same changes to correct all the data contained in the (Automated Data Correction) sheet.

    Also note, one constant that must be used between all three sheets each “Name” has specific data contained in the rows of cells to the right of column B and is to be used as a specific data reference.
    If there’s a way to combine a V-LookUp and an index/match it may work, but I’m not sure how exactly to write the equation to make it work.

    Please see the attached example spreadsheet for clarification. Just to highlight, the cells in the “Raw Data Export” sheet that are red indicate the incorrect data and the cells in the “Automated Data Correction” sheet indicate the automatically corrected data. The colors are not to be used in the final product and are just here in the attachment to help clarify.

    Thank you for your help and let me know if I can answer any further questions you may have.
    Attached Files Attached Files
    Last edited by garrett.grillo; 11-05-2012 at 12:54 AM.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Equation That Will Automatically Change Cell Data

    See formula in file attached.
    I suggest you use dynamic range names for your raw table and your manual modification table. This will make the formula self adjust to any addition to your tables.
    regards
    Attached Files Attached Files
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Contributor garrett.grillo's Avatar
    Join Date
    06-19-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Equation That Will Automatically Change Cell Data

    Hi Pierre,

    You did it!!!

    Just when I didn't think it was possible to do after so many people look at my post and passed it on, you figured it out!

    I had two questions after looking at your updated version of the excel example attachment:

    1) Cell C7 in the Automated Data Correction sheet does not include the IFERROR portion of the equation like all the other cell equations do. Should I change cell C7 to have the IFERROR portion of the equation as well or remove all of the IFERROR equations from the other cells leaving it all up the V-LookUp equations?

    2) I'm not familiar in creating dynamic range names and what this will do for my data collection between the three sheets of information?

    A BIG thank you again!

    Garrett

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Equation That Will Automatically Change Cell Data

    First, C7 should have the "iferror" version of the formula. It is an error from me.
    Second, let me modify the file with dynamic range today and you'll see what I mean.

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Equation That Will Automatically Change Cell Data

    Here is the file modified with named ranges. If you go in the "Formula" menu and click on the "Name manager", you'll find 2 named defined: Manual_Modif and Raw_Data. Look at the formula at the bottom. It is like this:
    Please Login or Register  to view this content.
    It automatically enlarges itself in the rows direction as you put more data in the table.
    You then use those name in a formula that can be easily copied from preceding rows. Formula is self-adjusted.
    Good luck
    Attached Files Attached Files

  6. #6
    Forum Contributor garrett.grillo's Avatar
    Join Date
    06-19-2011
    Location
    San Francisco, California
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Equation That Will Automatically Change Cell Data

    Pierre,

    You’re a dam Excel Rock Star my friend!!!

    Thank you incredibly for all your help here!

    Please let me know if I can send anymore praise your way here on the Excel Forum.

    Garrett

+ 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