+ Reply to Thread
Results 1 to 6 of 6

Cell references changing within formula at random

  1. #1
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Cell references changing within formula at random

    I have a table with 20 columns. The first 5 columns are for data entry. One column uses a sample code with multiple parts to describe aspects of the sample. The remaining columns contain formulas that extract portions of the sample code resulting in the text equivalent of a part of the code. What appears to be random, the formulas will change the row reference and extract data from a different row than from the same row. Here are some examples:

    IF(C3="","",LEN('Main Page'!$C3)) (The first row)
    IF(C289="","",LEN('Main Page'!$C289)) (The last row with the correct row reference)
    IF(C290="","",LEN('Main Page'!$C573)) (The next row, and so on)
    IF(C291="","",LEN('Main Page'!$C574))
    IF(C320="","",LEN('Main Page'!$C603))
    IF(C321="","",LEN('Main Page'!$C290))

    It appears that something was inserted at row 289 that restarted in row 321.
    Any ideas?

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Cell references changing within formula at random

    so lock row $C$289
    or better
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Cell references changing within formula at random

    I was unsure about your suggestion. This workbook contains about 12 worksheets all linked to Main Page where all the data entry takes place. The sample code is divided up into 4 parts: The water source (2 characters), the sample date (6 characters); or the batch code (8 characters); the category of sample (product, investigation, etc.) and the source. The column, Number of Characters in Code is a check that the Sample Code has the correct format. There is a separate spreadsheet for each source. I didn't understand your request to include a sample workbook, and was hesitant to do so because of the sheer size. What you are seeing is a fraction of the number of data entries. There are over 2000 entries. I hope what I have provided is sufficient for your review and analysis.

    The original thread presented just one scenario. The column doesn't always skip at C289. It is totally random where the row references get wacky. If I looked at the spreadsheet on another day, it might skip at c49 or c950. C289 was what was on that day. Does my explanation make sense or did I make things murkier?
    Last edited by vtentarelli; 05-22-2017 at 07:21 PM.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Cell references changing within formula at random

    =IF(C288="","",LEN('Main Page'!$C288))
    =IF(C289="","",LEN('Main Page'!$C289))
    =IF(C290="","",LEN('Main Page'!$C290))
    =IF(C291="","",LEN('Main Page'!$C291))
    =IF(C292="","",LEN('Main Page'!$C292))
    =IF(C293="","",LEN('Main Page'!$C293))
    =IF(C294="","",LEN('Main Page'!$C294))
    =IF(C295="","",LEN('Main Page'!$C295))
    =IF(C296="","",LEN('Main Page'!$C296))
    =IF(C297="","",LEN('Main Page'!$C297))
    =IF(C298="","",LEN('Main Page'!$C298))
    =IF(C299="","",LEN('Main Page'!$C299))
    =IF(C300="","",LEN('Main Page'!$C300))
    =IF(C301="","",LEN('Main Page'!$C301))
    =IF(C302="","",LEN('Main Page'!$C302))
    =IF(C303="","",LEN('Main Page'!$C303))
    =IF(C304="","",LEN('Main Page'!$C304))
    =IF(C305="","",LEN('Main Page'!$C305))
    =IF(C306="","",LEN('Main Page'!$C306))
    =IF(C307="","",LEN('Main Page'!$C307))
    =IF(C308="","",LEN('Main Page'!$C308))
    =IF(C309="","",LEN('Main Page'!$C309))
    =IF(C310="","",LEN('Main Page'!$C310))
    =IF(C311="","",LEN('Main Page'!$C311))
    =IF(C312="","",LEN('Main Page'!$C312))
    =IF(C313="","",LEN('Main Page'!$C313))
    =IF(C314="","",LEN('Main Page'!$C314))
    =IF(C315="","",LEN('Main Page'!$C315))
    =IF(C316="","",LEN('Main Page'!$C316))
    =IF(C317="","",LEN('Main Page'!$C317))
    =IF(C318="","",LEN('Main Page'!$C318))
    =IF(C319="","",LEN('Main Page'!$C319))
    =IF(C320="","",LEN('Main Page'!$C320))
    =IF(C321="","",LEN('Main Page'!$C321))
    =IF(C322="","",LEN('Main Page'!$C322))

    sorry but I don't see anything wrong as you said in post #1

    Mybe you copied down formulas with filtered column and next filter was removed, but maybe only
    Last edited by sandy666; 05-22-2017 at 07:39 PM. Reason: typo

  5. #5
    Registered User
    Join Date
    02-19-2014
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Cell references changing within formula at random

    Your right. That is the randomness of the problem. There is some event or activity that will randomly change the cell references. The original thread exhibited such randomness. I can correct the error by copying the formula in the first row and pasting to all rows below it. What you see is the result of that manipulation. I don't know what causes the randomness of the cell references, but when I encounter this again where I see this problem occur, I will try to isolate what I did to cause it.
    One way to tell if the row references are off is to look at Column G in your example. If the number in Column H does not match to the # of characters in Column C, an error message will appear in Column G, although the wrong number of characters is only 1 factor in causing Column G to display an error.
    That is the difficulty when the error appears at random.

    thanks for your attention to this matter. I can't claim that the problem is solved, but I think you deserve a high rating for your review and comments. I'll look into your suggestion about the filtered columns.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Cell references changing within formula at random

    With hidden rows - formula is copied correctly
    With frozen row - the same
    With filtered rows - formula is copied to the visible cells, invisible will stay empty
    wwfilter.jpg
    colA = source
    colB = formula: =A2 (without filter)
    colC = formula: =A2 (with filter by 5 and then the filter was removed)

    but order of references is correct and logical.

+ 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. How do I copy a formula without changing cell references?
    By Kdhowery in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 02:14 PM
  2. Drag formula down without changing one of the references
    By twisted31 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2013, 07:31 PM
  3. [SOLVED] Changing the cell that a formula references using command button
    By Sinnie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2013, 09:35 AM
  4. [SOLVED] How to copy formula's from a Relative cell without changing the cell references
    By mowens74 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-03-2013, 04:42 AM
  5. [SOLVED] Cell references in formula keep changing when moving data in referenced cells
    By sam beginer in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2013, 09:56 PM
  6. Formula in cells with changing references
    By Idiotfool in forum Excel General
    Replies: 3
    Last Post: 03-16-2009, 09:41 AM
  7. [SOLVED] Formula automatically changing cell references
    By Nina in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2006, 06:00 PM
  8. Changing cell references
    By Tracey in forum Excel General
    Replies: 4
    Last Post: 01-04-2005, 05:06 PM

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