+ Reply to Thread
Results 1 to 16 of 16

True Value and referenced not working

  1. #1
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    True Value and referenced not working

    I've been copying this formula down from cell to cell as I add vehicles to my list. However, the last two vehicles for some reason is showing "VALUE#!"
    Please Login or Register  to view this content.

    F8 on the tab that is referenced is showing "N/A", which is working not he previous tabs
    Please Login or Register  to view this content.
    A24, 8th column has the milage entered as "40017"

    Any ideas why it's not working now?
    Last edited by CLSSY56; 10-12-2021 at 03:55 PM.
    -Travis
    Mac OS 12.1
    Excel 365 (v16.59)

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: True Value and referenced not working

    Attach a sample workbook - instructions on how to do this are given in the yellow banner at the top of the screen.

    Pete

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

    Re: True Value and referenced not working

    Edit: Pete beat me to it.

    Also please update your profile. Location (country only) and Excel version are often a factor in solutions.
    Last edited by FlameRetired; 10-12-2021 at 04:01 PM.
    Dave

  4. #4
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    Hey Pete! This is the same workbook as before, just a strange problem...
    https://www.excelforum.com/excel-for...ll-is-0-a.html

  5. #5
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    Pete, that file fairly complex and I'm not sure how I can simplify it to produce the same error. Do you want me to send you the actual file?

  6. #6
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    How many tabs can a workbook manage and reference? I have ~92 in this workbook at the moment...
    Last edited by CLSSY56; 10-12-2021 at 04:46 PM.

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

    Re: True Value and referenced not working

    Thanks for updating your profile. (365 has a lot of advantages the older version doesn't.)

  8. #8
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    How do I simplify this
    Please Login or Register  to view this content.
    Something like... (I don't understand very well)
    =($G24+5000)>($H24-500), True

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: True Value and referenced not working

    I couldn't see how the formula related to the file attached to your earlier thread (given in Post #4), and also I didn't understand why you had @ before the INDIRECT function names - that's a throwback to LOTUS-123 and Quattro. By all means, attach a more up-to-date file.

    In answer to the last question, try this:

    =($G24+5000)>($H24-500)

    This will return TRUE or FALSE, as appropriate.

    Hope this helps.

    Pete

  10. #10
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    I did simplify the spreadsheet and it does still have the same errors

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: True Value and referenced not working

    I think the problem with the workbook is that the final two entries in the Master sheet, i.e. cells A6 and A7, are numeric values, whereas the cells above are formatted as text. You can put this formula in L3:

    =ISTEXT(A3)

    and copy it down to L7 to confirm this yourself.

    I used the Format Painter icon to copy the format from A4 down into those two cells, and pressed the F2 key on each as if to edit the cell and pressed Enter without changing anything, and then the #VALUE errors in J6 and J7 disappeared (to show blanks). If you look at F7 in the sheets 86 and 87 you will see that the #N/A has gone and been replaced by numbers. Is this what you were expecting?

    Hope this helps.

    Pete

  12. #12
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    I'm not sure if I follow that right...
    Last edited by CLSSY56; 10-13-2021 at 11:47 AM.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: True Value and referenced not working

    In the Home tab, it is directly under the Copy icon, towards the left-hand side. Select cell A4, then click on the Format Painter icon, then move the mouse pointer to select A6 and A7, then release.

    Hope this helps.

    Pete

  14. #14
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    That also copies the conditional formatting as well...

  15. #15
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    172

    Re: True Value and referenced not working

    OK, I got it to work doing a different method. Just copied row for 84 and renamed it and edited the hyperlink. The workbook is working now. I must have copied 85, which wasn't working, for 86 & 87.

    You mentioned earlier that the formula looked weird with "indirect", is there a better way to do those formulas?
    Last edited by CLSSY56; 10-13-2021 at 12:05 PM.

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: True Value and referenced not working

    In your first post the formula included several @INDIRECT names, which is what I was commenting on. I didn't see the @ symbol in the file you attached to Post #10.

    As you want to choose different sheet names from a cell reference, then INDIRECT is the way to do it, although this is a volatile function which may make your workbook sluggish if used many times (as it will recalculate every time there is a change).

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Replies: 5
    Last Post: 07-27-2021, 12:17 AM
  2. Replies: 6
    Last Post: 03-17-2019, 02:20 PM
  3. Replies: 3
    Last Post: 08-16-2018, 06:07 AM
  4. =AND() not working as expected. Two true functions don't give true result
    By rectorsquid in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-30-2018, 04:34 PM
  5. Help popup window if condition is TRUE in change event if conditon is true
    By fanku in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-09-2014, 12:46 PM
  6. how to make checkbox true selecting then chaning 2nd box true when null
    By ferrum_equitis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2012, 01:13 PM
  7. [SOLVED] Function to return True/False if all are validated as True by ISNU
    By Tetsuya Oguma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2006, 06:30 AM

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