1. ## RailRoad #NA Error (1997)

I have a #NA error when using the following formula which is located in the Pay Scales sheet: The book has two sheets entitled Timesheet and Pay Scales.

=IF(Timesheet!S3=TRUE,"",VLOOKUP(Timesheet!S3,\$E\$3 :\$L\$302,6)*43/480)

S3, of course is in the Timesheet sheet, and my lookup table, and the cell where this formula is located is in the Pay Scales sheet. What am I doing wrong?

Also, for some reason I cannot use my arrow keys to move between cells. The only way I can get from one cell to another is with either the tab key or the mouse.

There is also a math formula that cannot figure out. I have two rates of pay, which represent an 8 hour day (\$172.87 and \$108.78). an employee gets paid 20 minutes at overtime for not being allowed to take a meal period. He/She is paid \$10.37 for this penalty. I know that thie and one-half on 20 minutes comes out to 30 minutes straight time, but cannot figure out how they come up with the \$10.37 dollar amount. I suspect that the \$108.78 figure is used, because other formula's use that same figure.

Thanx

Larry

2. ## Re: RailRoad #NA Error (1997)

Steve (sdckapr) has been answering all your questions, but he will be unavailable for a week or so. The rest of us don't know enough about your workbook, I'm afraid, so you'll have to excercise patience until Steve is back.

3. ## Re: RailRoad #NA Error (1997)

Thanks Hans. I appreciate the reply.

Larry

4. ## Re: RailRoad #NA Error (1997)

Quest 1:
Vlookup is returning #NA. What is the value of TimeSheet!S3? If the value is < than the first item in the list it will return #NA since your implication is that the list E3:E302 in in ascending order. Is the list in ascending order or do you want to find an exact match? Use this for exact match:

=IF(Timesheet!S3=TRUE,"",VLOOKUP(Timesheet!S3,\$E\$3 :\$L\$302,6,False)*43/480)

(This will give #NA if item in S3 is not in the list)

Quest 2:
Unsure. Do you have macros running to disable? Or a macro which only allows certain selections? Is it all sheets or just this? Is NUMLOCKS on keypad? Could you give more details of what you have tried?

Quest 3:
Is the 10.37 a "given dollars"? If given you will have to ask whomever gave it to you. Is the result of a formula? What is the formula?

Based on the 2 numbers: 172.87 and 108.78, I figure it is a little under 28.8 min straight time for the 1st eqn or a little under 45.8 min for the 2nd. It doesn't seem to come directly from them. Perhaps there is another factor?

Steve
PS Hans is correct, I am out of town and am only checking periodically, so you might not here from me again till the end of the year.

5. ## Re: RailRoad #NA Error (1997)

I have the money problem resolved. Timekeeping had a wrong formula.

Thanks for the formula correction. Now I want to take S3 ( in the Timesheet sheet) and the formula that you corrected [=IF(Timesheet!S3=TRUE,"",VLOOKUP(Timesheet!S3,\$E\$3 :\$L\$302,6,False)*43/480)] which is in the Pay Scales sheet, and have the formula follow column S. In other words, have that formula (which is stationary in cell B36 in the Pay Scales sheet) calculate rates of pay as new rows are filled in (S3, S4, S5 etc) in the Timesheet sheet. Possible or not?

Larry

6. ## Re: RailRoad #NA Error (1997)

Steve:

Do you think the reverse use of column S will work? Please review my previous post.

Larry

7. ## Re: RailRoad #NA Error (1997)

Which previous post? {note you can reference a post with by enclosing (no quotes) "Post#=xxxxxxx" inside brackets ([ ]).

I also do not know what you mean by "the reverse use of Column S" so do not know if it will work.

Steve

8. ## Re: RailRoad #NA Error (1997)

I want to take S3 ( in the Timesheet sheet) and the following formula [=IF(Timesheet!S3=TRUE,"",VLOOKUP(Timesheet!S3,\$E\$3 :\$L\$302,6,False)*43/480)] which is in the Pay Scales sheet, and have the formula follow column S. In other words, have that formula (which is stationary in cell B36 in the Pay Scales sheet) calculate rates of pay as new rows are filled in (S3, S4, S5 etc) in the Timesheet sheet. Possible or not?

9. ## Re: RailRoad #NA Error (1997)

I don't understand what you want?
If you copy this formula down the rows (in B37, B38, etc) it will work on S4, S5, etc.
If that is not what you are after, you need to be more specific.

Steve

10. ## Re: RailRoad #NA Error (1997)

I want to use cell B37 in a certain sheet for the following:

S3 ( in the Timesheet sheet) and the following formula which is in the sheet that holds my lookup tables [=IF(Timesheet!S3=TRUE,"",VLOOKUP(Timesheet!S3,\$E\$3 :\$L\$302,6,False)*43/480)] would pay 43 minutes for a certain arbitrary. If a person in S4 were to work a different position, this 43 minute pay would be different.

Column S will always change per the above formula. I want to take fell B37 (where the above formula is stored), so to speak, in this lookup table and devise a formula tagged to column S. In other words, as S3, S4, S5 etc. are input, Cell B37 will automatically look at the latest entry as column S goes down the line and changes.

For instance, a person is entitled to several arbitraries, and one of them would be XXX (worth 43 minutes pay). I could assign this arbitrary to cell B37 in the sheet that holds my data tables (lookup tables). Depending upon which position a person would work (Column S), this arbitrary would change as new rows are added to (S3, S4, S5 etc.).

Possible?

Larry

11. ## Re: RailRoad #NA Error (1997)

Instead of "TimeSheet!S3" you could use
vlookup(9.99999999999999E+307,TimeSheet!S:S,1)

so you formula becomes:
=VLOOKUP(vlookup(9.99999999999999E+307,TimeSheet!S :S,1),\$E\$3:\$L\$302,6,False)*43/480)

This will find the last number in column S It will ignore blanks and non-numerics.

Steve

12. ## Re: RailRoad #NA Error (1997)

Thanx Steve. Will place it into the formula, and make adjustment's. why the +307 in the formula. Do I have to adjust other cells accordingly? (I have over 200 different cells in a lookup table I want to use this type of formula in, making reference to.

13. ## Re: RailRoad #NA Error (1997)

The formula uses a "trick" in excel. That value is the largest possible number that excel can use. When it looks thru a list that it assumes is sorted in ascending order, it will keep looking till the end of the list or until it finds a number larger than the "target". since it won't find one larger, it will "extract" the last number in the list.

If you are going to use it alot, I would create a name for the "Large number" and just reference that name in the cell:
vlookup(BigNum, LookupList, Range,1)

Steve

14. ## Re: RailRoad #NA Error (1997)

Thanx Steve. Is there a way that I can utilize this formula and hava the words "Auto Calculated" appear on the screen, and have the formula working in the background?

Larry

15. ## Re: RailRoad #NA Error (1997)

To pop a message on the screen will require code.

What are you trying to accomplish?
If calculations are slow, you could turn off auto-calc and just calculate "on demand" (tools-options - calculation manual).

If the spreadsheet is complex enough, it might speed up by coding all the calcs: read into VB the range(s) of interest, manipulate them, get the results formatted as desired and then, "dump it" in the worksheet. I have found in some cases that this could be done faster that using formulas.

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•