+ Reply to Thread
Results 1 to 6 of 6

Cell references in formulas/conditions

  1. #1
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Cell references in formulas/conditions

    I'm trying to create some formulas and conditions to pull information from one workbook to another; however, I keep running into #Value! errors. I'm wondering if it is a reference issue. I say this because one piece of information I am referencing is my company's seed variety type, or a common identifier that our salesmen refer to each individual product. For instance, one product number is "39ry43". Could the issue be that excel is getting confused when it sees numbers and text that are combined? Thoughts/suggestions?

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Cell references in formulas/conditions

    Alphanumerics are always seen by Excel as text.......... There are a number of reasons you will see #Value errors and they depend on the type of calculation you are performing. It can basically read as "one or more of the input values make this formula impossible"
    Elegant Simplicity............. Not Always

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Cell references in formulas/conditions

    Do you know that you have to open both workbooks?
    Post your formula or better your xls file.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Cell references in formulas/conditions

    I have both workbooks side-by-side.

    Here is a generic example of what I have now:
    =IF('[File1.xlsx]Sheet1'!$C$8='[File2.xlsx]Sheet2'!$D$2,IF('[File1.xlsx]Sheet1'!$F$8='[File2.xlsx]Sheet2'!$A$6,'[File1.xlsx]Sheet1'!$E$8,""),"")
    This formula is returning a blank cell...which is correct!

    Here is a generic example of what I want to do:
    =IF('[File1.xlsx]Sheet1'!$C$8:$C$100='[File2.xlsx]Sheet2'!$D$2,IF('[File1.xlsx]Sheet1'!$F$8:$F$100='[File2.xlsx]Sheet2'!$A$6,'[File1.xlsx]Sheet1'!$E$8:$E$100,""),"")
    This returns my #Value! error.

    I want File2 to search an array in File1 ($C$8:$C$100), and when File2 finds the specified criteria ($D$2) I want it to then search another array in File1 ($F$8:$F$100) to find another specified criteria ($A$6). When both criterion are met ($D$2 and $A$6) I want File2 to search a 3rd array in File1 ($E$8:$E$100) to find the corresponding value to output to File1. If one or none of the first 2 criterion are met, I want the output to be a blank cell. Nutshell, both of the first 2 criterion must be met before I want a value to be output. Make sense? Is this even possible with IF?

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Cell references in formulas/conditions

    Can you upload the file?

  6. #6
    Registered User
    Join Date
    05-08-2013
    Location
    Effingham, IL, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Cell references in formulas/conditions

    Can't upload files due to sensitive information. Took a screenshsot of both files side-by-side and saved it in MSWord.

    screenshot.docx

+ 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