+ Reply to Thread
Results 1 to 10 of 10

Can't reference cell in another sheet properly

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    5

    Can't reference cell in another sheet properly

    So, I've run into an odd issue I haven't seen before with Excel 2016. I am doing a simple cell reference to another sheet in the same workbook. Literally just: ='Sep 5'!X8:AF8

    So in my example, I am taking data from a merged set of cells in the sheet 'Sep 5' and using ='Sep 5'!X8:AF8 to copy that to a cell range in the Sep 12 sheet. If I place that formula into any cell in the target worksheet, I get a #VALUE error. The only exception is if I place it into X8 on the target sheet. It works fine. If I place it into any of cells Y8 thru AF8, it returns 0. Actually it work in the entire X column, but no other cell. I've tried it with relative and fixed reference as well.

    As I type this, it is tickling parts of my brain like I knew this problem and answer, but for the life of me I can't figure it out. All cells are formatted General.

    Untitled.png

  2. #2
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    MS365 (PC) v.2108
    Posts
    257

    Re: Can't reference cell in another sheet properly

    Can you post a sample workbook? I'm not sure I'm understanding what you're trying to do.

  3. #3
    Registered User
    Join Date
    09-18-2017
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    5

    Re: Can't reference cell in another sheet properly

    Here you go. Basically I just want the data from 'Sep 5'!X8:AF8 to be copied to 'Sep 12'!T8:W8.
    Attached Files Attached Files

  4. #4
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    MS365 (PC) v.2108
    Posts
    257

    Re: Can't reference cell in another sheet properly

    Not sure what's going on; I didn't think a single cell could "equal" a range of cells, thought that might have been the issue. Hopefully someone in here will be able to assist.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Can't reference cell in another sheet properly

    garymoore Unfortunately what you have ... =RIGHT('Sep 5'!X8:AF8,5) ... is an array formula. They cannot be entered in merged cells.

    If you are not familiar with array formulas they are not like regular formulas. They must be committed from edit mode by simultaneously holding down Ctrl + Shift while hitting Enter. You can try this in a non merged cell to see it work.

    Beyond that I can not tell what you are trying to achieve unless it is this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It does not have to be array entered.

    Alternatively you might try unmerging 'Sep 5'!X8:AF8. The text "WORKS" only exists in 'Sep 5'!X8. There will be nothing but blanks in 'Sep 5'!Y8:AF8
    Last edited by FlameRetired; 09-18-2017 at 05:51 PM.
    Dave

  6. #6
    Registered User
    Join Date
    09-18-2017
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    5

    Re: Can't reference cell in another sheet properly

    FlameRetired, sorry about that. You caught a cell that I was playing around with to see what the issue was. Still, the cell above the one you saw has the same issue and it is not an array formula. (Is RIGHT really an array? It requires no special actions to iterate or save)

    I've attached the sheet again. you should be able to see the problem. All I want to do is have the cell copy over the data from another sheet in the same book, using ='Sep 5'!X8:AF8
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-18-2017
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    5

    Re: Can't reference cell in another sheet properly

    So, I have done this a lot, copy one set of cells (X8:AF8) over to another cell or range of cells. It works all the time. Excel has never seemed to care about matching ranges, etc. This happens frequently when you have a few cells merged for sizing or cosmetic reasons. Once merged, those cells cannot be selected individually and the formula ends up including all cells in the merged set.

  8. #8
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: Can't reference cell in another sheet properly

    If I were to copy a set of cells, I would put ='Sep 5'!X8 into the new location. You do not need the whole range of merged cells. The reason you are getting a #VALUE error is because it is trying to copy column X into T and the relative shift is to a smaller range of cells. It will work with just the left reference cell. Hope this is clear enough.

  9. #9
    Registered User
    Join Date
    09-18-2017
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    5

    Re: Can't reference cell in another sheet properly

    That's how I would do it, too. If I were to type in the formula directly. But when I am selecting the cell (using my mouse) then it is automatically selecting a range of cells. I can work around this issue, but I'm curious what is happening that is causing this. Seems like some odd setting within excel? I'm beginning to believe that a fresh install may be the way to go.

  10. #10
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Pendleton, OR
    MS-Off Ver
    Excel 2013
    Posts
    129

    Re: Can't reference cell in another sheet properly

    I see. Yes, it has to do with the mouse selection when cells are merged. It may be a setting in Excel, but I don't know it. The problem as I see it is trying to reference 5 cells in 3, but you had said you had done this before so maybe it is a change in Excel settings. Good luck.

+ 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. Indirect Sheet reference with cell reference left function and text
    By teststrip in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2015, 10:31 AM
  2. [SOLVED] Conditional formatting formula cell reference not incrementing properly
    By killerthun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2014, 07:29 PM
  3. [SOLVED] =INDIRECT, Sheet reference with Incremental cell reference
    By Deap in forum Excel General
    Replies: 4
    Last Post: 06-16-2014, 05:58 AM
  4. [SOLVED] Calling string inside cell reference to reference another sheet.
    By {=OR(value=array)} in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2013, 04:45 PM
  5. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  6. how to reference sheets properly?
    By papermoon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2010, 11:00 AM
  7. [SOLVED] Nesting a sheet name reference within a cell reference???
    By Broyston in forum Excel General
    Replies: 6
    Last Post: 10-31-2005, 04:05 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