True Value and referenced not working

[CLSSY56]

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?

[Pete_UK]

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

Pete

[FlameRetired]

Edit: Pete beat me to it.

Also please update your profile. Location (country only) and Excel version are often a factor in solutions.

[CLSSY56]

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

[CLSSY56]

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?

[CLSSY56]

How many tabs can a workbook manage and reference? I have ~92 in this workbook at the moment...

[FlameRetired]

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

[CLSSY56]

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

[Pete_UK]

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

[CLSSY56]

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

[Pete_UK]

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

[CLSSY56]

I'm not sure if I follow that right...

[Pete_UK]

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

[CLSSY56]

That also copies the conditional formatting as well...

[CLSSY56]

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?

[Pete_UK]

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