Results 1 to 15 of 26
Thread: RailRoad #NA Error (1997)

20031221, 04:35 #1
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 onehalf 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

20031221, 13:10 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031221, 14:06 #3
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: RailRoad #NA Error (1997)
Thanks Hans. I appreciate the reply.
Larry

20031222, 20:23 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: RailRoad #NA Error (1997)
you asked several questions:
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.

20031223, 00:55 #5
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040106, 03:46 #6
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: RailRoad #NA Error (1997)
Steve:
Do you think the reverse use of column S will work? Please review my previous post.
Larry

20040106, 10:42 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040107, 23:13 #8
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20040108, 00:20 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040108, 00:40 #10
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040108, 02:19 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 nonnumerics.
Steve

20040108, 04:22 #12
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20040108, 10:59 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040108, 13:13 #14
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040109, 00:27 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 autocalc and just calculate "on demand" (toolsoptions  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