+ Reply to Thread
Results 1 to 8 of 8

Issue with INDIRECT/CONCATENATE

  1. #1
    Registered User
    Join Date
    08-10-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    10

    Issue with INDIRECT/CONCATENATE

    I am trying to connect one spread sheet to another in a Network drive using multiple cells to fill in the destination as shown in this image.
    9efeb94a7fd32a072841221b93d98b15.png

    I am getting a #REF error but seem to be able to use a simple formula to connect to the same sheet on the network.
    I have opened both sheets and used this formula multiple times.
    dbfb746a61b317918d91079c141100c8.png

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,452

    Re: Issue with INDIRECT/CONCATENATE

    INDIRECT only works on open sheets.
    Glenn



  3. #3
    Registered User
    Join Date
    08-10-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    10

    Re: Issue with INDIRECT/CONCATENATE

    Quote Originally Posted by Glenn Kennedy View Post
    INDIRECT only works on open sheets.
    I have the sheet open the whole time, still no luck

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,452

    Re: Issue with INDIRECT/CONCATENATE

    Without a sample sheet to play with... and the fact that I'm on a bus (!!)... hard to tell. But try deleting the ' before [Pricing sheet...

  5. #5
    Registered User
    Join Date
    08-10-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    10

    Re: Issue with INDIRECT/CONCATENATE

    It's okey lol I'm in no huge rush, I appreciate the help, I'd love to upload the sheet but wouldn't work without the correct network drive. I've tried deleting most of the formula I can and different variations . It's so weird it works everywhere else XD

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,452

    Re: Issue with INDIRECT/CONCATENATE

    Did you try the suggestion at Post 4??

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,704

    Re: Issue with INDIRECT/CONCATENATE

    Glenn has stated the visible error of the extra inverted comma between the file path and file name, there should only be 2 of them, 1 before the file path starts, and 1 between the sheet name and exclamation mark. The 3rd one is going to cause an error.

    The less visible possible error would be in $C$4, does that cell contain the word "May" or a numeric date formatted as "MMMM"? The latter of which will not give the correct file path.

  8. #8
    Registered User
    Join Date
    08-10-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    10

    Re: Issue with INDIRECT/CONCATENATE

    I seemed to have solved the issue sorry for not getting back to you quicker. It seems I am unable to fill in a file name with another cell, there may be a way to fix this but changing the D column to the file name and just referencing that seemed to work. Thank you for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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