Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    2 Star Lounger
    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 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. #2
    Plutonium Lounger
    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.

  3. #3
    2 Star Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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.

  5. #5
    2 Star Lounger
    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

  6. #6
    2 Star Lounger
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    2 Star Lounger
    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?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    2 Star Lounger
    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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 non-numerics.

    Steve

  12. #12
    2 Star Lounger
    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.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    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

  14. #14
    2 Star Lounger
    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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 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 LastLast

Posting Permissions

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