+ Reply to Thread
Results 1 to 4 of 4

indirect if formula issues

  1. #1
    Registered User
    Join Date
    08-01-2014
    Location
    Worcester, Massachusetts
    MS-Off Ver
    2010
    Posts
    3

    indirect if formula issues

    Hi everyone,

    I'm currently working on an excel spreadsheet that allows a user to paste pre-generated values in, sorts the values through a macro and then paste a specific column to a new sheet. This new sheet then uses if statements to draw further information. It was working fine, however I need the user to able to use the spreadsheet with any set of data, not just the original data I inputted. I have been trying to use the following statement to read off the original sheet, and judging on the value, output a specific answer:

    =IF('Record Entry Number'!$C3=INDIRECT(Transactions!$G3),IF(INDIRECT(Transactions!$J3<0),INDIRECT(Transactions!$J3,0)),"")

    However, I am just coming up with #REF! whenever I run the macro. Does anybody know why my formuals are not reading and if so, how to fix them?

    Thanks!

    GotJ

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: indirect if formula issues

    whats in transactions g3?

    INDIRECT(Transactions!$J3<0) is wrong probably should be
    INDIRECT(Transactions!$J3)<0
    probably
    it should read
    =IF('Record Entry Number'!$C3=INDIRECT(Transactions!$G3),IF(INDIRECT(Transactions!$J3)<0,INDIRECT(Transactions!$J3),0),"")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    08-01-2014
    Location
    Worcester, Massachusetts
    MS-Off Ver
    2010
    Posts
    3

    Re: indirect if formula issues

    g3 conatins a numeric value that is either positive or negative (never zero). Bacially, the sheet "transactions" has names and associate values that I need to pull into a seperate sheet "Record Entry Number". My goal was to confirm that the names matched (the first if), and then pull the data if the number was less than zero (second if). The one catch was that I needed this formula to work if I pasted new data into "transactions"

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: indirect if formula issues

    =INDIRECT(Transactions!$G3)
    now you say transactions g3 contains say 5
    =indirect(5) that is trying to create a reference there is no such reference as 5
    now if Transactions!$G3 had say text of sheet1!a1
    then
    =INDIRECT(Transactions!$G3) would return the contents of sheet1!a1

+ 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. [SOLVED] Date/INDIRECT Issues
    By liquidtrails in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 01-21-2014, 04:44 PM
  2. Replies: 3
    Last Post: 11-28-2013, 02:11 AM
  3. [SOLVED] Issues with VLOOKUP/INDIRECT and retrieving from multiple sheets
    By nicolerork in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-28-2013, 12:53 AM
  4. Worksheets, Indirect References and Performance Issues
    By Rikkdh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2013, 09:36 AM
  5. [SOLVED] Having issues with INDIRECT and cell references
    By qaliq in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-31-2013, 06:10 AM

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