Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    If and VLookup formulas in same cell (XP & 2003)

    I have attached a zipped workbook. I want to populate specific columns with certain conditions have to be met. I used vlookup formulas to populate the cells however since if there is no updated population for a given month then the amount should be carried forward from the prior month. I have a set of columns called the BEG. for the start of the population process. I have two columns in each month to update the amount or carry it foward from the prior month. One is "FTE/SCHED HRS STD" and the second column is " (NAME OF MONTH) RATE/HR".

    I prepared one w/s tab name "DEMONSTRATION ONLY NO FORMULA" for demonstration highlighting the cells in red where the update should occurr and the rest of the cells in blue where there was a carry forward. The yellow columns are the beginning of the year amounts and no decision has to be made on them.

    I have the acutual vlookup formulas in w/s tab "MONTHLY FTE HRS & RATE PER HOUR"

    I apologize for being long winded.

    I hope some of you Gurus has an answer other than dismantelling my work book. I was thinking of some sort of combined "IF and VLOOKUP" formula.

    Thanks

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    I can't download from the lounge here at work so I can't see what's in your attachment, but the if/vlookup formula would presumably look something like this:

    if(vlookup(...) <> [cell where last month's figure is], vlookup(...), [cell where last month's figure is])

    Does that help?
    Beryl M


  3. #3
    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: If and VLookup formulas in same cell (XP & 2003)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>I don't understand what you are trying to accomplish. Could you explain the logic of how to determine what is updated and what is carried forward and what you want to do with them.

    Also what cells do you need formulas in and what/how should they be calcuated?

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    Steve,

    I will only address the columns labeled "FTE/SCHED HRS STD" since the logic follows for the columns labeled " month RATE/HR".

    My objective is to populate columns I,S & AC with either the "BEG FTE/SCHED HRS STD" or the latest update amount from the vlookup formula in the particular cell. One the month has found an update then it is carried forward to the subsequent months until another update has been found via the vlookup formula. if there is no updates in subsequent months then the beginning amount is carryforward to the subsequent months.

    Hopefully, this is more understandable.

    Thanks

    Martin

  5. #5
    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: If and VLookup formulas in same cell (XP & 2003)

    Sorry, but I still don't understand:

    I can see that currently col I looks up the file_no (from Col E) in the 'BEG SALARY' sheet and gets the appropriate value from Col H ("FTE/SCHED HRS STD").

    Col S does a "Double-lookup" looking the file_no (from Col E) and in addition finding when the "MONTH OF INCREASE" (Col N) is equal to 8 (=August) in the 'HR DELTA' sheet and gets the appropriate value from Col M ("APPT_SCHED_HOURS").

    Col AC is the "same" as col S except it uses a 9 (=Sept) as the "MONTH OF INCREASE".

    I can see what you have, i do not understand what you want to be in those cells.

    Could you clarify where you want to get or how you want to calculate the values for those cells?

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    Basically, I want to use the amount in the column G " Beg. FTE/SCHED HRS STD for columns I, S & AC unless column I via a VLOOKUP populates with a number then Columns I along with columns S & AC will record that new updated number from column I. This will be the case unless the subsequent columns either column S via VLOOKUP finds a new updated amount then that amount is recorded in column S and column AC unless column AC finds via VLOOKUP a new amount then that new amount is recorded in the column AC.

    so let's say column G has 35 and VLOOKUP for column column I & S show 0 then, from column G, 35 is recorded in column I and 35 is recorded in column S. lets say that column AC has a vlookup value of 40. So column AC changes the series to 40.

    Does it make sense yet?

  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: If and VLookup formulas in same cell (XP & 2003)

    Still not sure I understand, but if you want the value in I, S, AC to not be zero if there is not a "found value" change the 0 in the formula to what you want Eg from Col G)

    For in I9
    =IF(ISNA(VLOOKUP($E9,'BEG SALARY'!$B$3:$I$31,7,FALSE)),G9,VLOOKUP($E9,'BEG SALARY'!$B$3:$I$31,7,FALSE))

    In S9
    =IF(ISNA(VLOOKUP($E9&V$8,'HR DELTA'!$K$3:$T$45,3,FALSE)),G9,VLOOKUP($E9&V$8,'HR DELTA'!$K$3:$T$45,3,FALSE))

    In AC9:
    =IF(ISNA(VLOOKUP($E9&AF$8,'HR DELTA'!$K$3:$T$45,3,FALSE)),G9,VLOOKUP($E9&AF$8,'H R DELTA'!$K$3:$T$45,3,FALSE))

    If you are looking for something else, you will have to clarify: Perhaps pick an example cell that is not calculated correctly and tell us how it should be calculated and the logic.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    Hi Steve,

    What is required is the "Beg" column is the base amount . Then each subsequent column for the category has a decision to be made. Is there an amount coming in for July via the Vlookup formula? If there is then record the new amount. If not then record the amount from the prior month. July can only go to the Beg column amount because that is the 1st or base amount. In August the decision to be made: Is there an amount coming in for August via Vlookup formula ? If there is then record that new amount. If not then use the July amount.

    The iteration on the above decision keeps on sliding over from month to month, but each cell has to be completed by either recording the prior month's amount or populating the cell with a new amount from the Vlookup formula.

    I hope this is a little more defined.

    Thanks,

    Martin

  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: If and VLookup formulas in same cell (XP & 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 30-Sep-05 18:27. added PS)</P>Instead of G9, replace for the other months, with the cell from the previous month from whatever column that happens to be in. That way if a new one is not found it will use the last month's value.

    Steve
    PS
    I assume I9 will use G9, S9 will use I9 and AC9 will use S9, etc

  10. #10
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    Hi Beryl,

    I just sent a response to Steve which, demonstrates the decision process on the if statement to be made. Your illustration seemed to be on the right track.

    Thanks,

    Martin

  11. #11
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    The first decision formula if ther are any starts with column I. That decision is either the formula found a new amount in the Vlookup or it did not find an amount and uses the prior amount which is the base column ie., the BEG amount in column G.

    So an "if foumula along with a Vlookup formula" if that is possible.

    Martin

  12. #12
    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: If and VLookup formulas in same cell (XP & 2003)

    Doesn't what I suggest in my last response do just that? Here they are explicitly:

    For in I9
    =IF(ISNA(VLOOKUP($E9,'BEG SALARY'!$B$3:$I$31,7,FALSE)),G9,VLOOKUP($E9,'BEG SALARY'!$B$3:$I$31,7,FALSE))

    In S9
    =IF(ISNA(VLOOKUP($E9&V$8,'HR DELTA'!$K$3:$T$45,3,FALSE)),I9,VLOOKUP($E9&V$8,'HR DELTA'!$K$3:$T$45,3,FALSE))

    In AC9:
    =IF(ISNA(VLOOKUP($E9&AF$8,'HR DELTA'!$K$3:$T$45,3,FALSE)),S9,VLOOKUP($E9&AF$8,'H R DELTA'!$K$3:$T$45,3,FALSE))

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    Steve,

    The formula in cell I9 must be able to make the decision that if there is recorded hours in the "HR DELTA" w/s for this particular file, it should record that number. If there isn't any number in "HR DELTA" then record the previous recorded amount. In this case Beg FTE/SCHED HRS STD amount.

    If it were the cell S9 then the decision would be to find a number for August if none then use the numer used for July. and so on....

    I apologize, the w/s is not straight forward, but I9 , S9 or AC9 must answer the same questions. Is there a number in the HR DELTA w/s for that particular month, if yes record it. If no, record the prior months number.

    Martin

  14. #14
    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: If and VLookup formulas in same cell (XP & 2003)

    S9 and AC9 do that don't they? In s9 if it is not found in Hr delta, it uses the value from I9 (the previous month), if it is found it use the value found.
    In AC9 if it is not found in Hr delta, it uses the value from S9 (the previous month), if it is found it use the value found.

    If these are not correct could you specify with an example workbook to demonstrate where they fail? Also please explain what numbers they should be. As far as I could tell in your example sheet, those calcs got what you wanted...

    If you want I9 to work the same (look in HR delta and if not found use the value in Beg salary and if found use the value in hr delta:

    =IF(ISNA(VLOOKUP($E9&L$8,'HR DELTA'!$K$3:$T$45,3,FALSE)),VLOOKUP($E9,'BEG SALARY'!$B$3:$I$31,7,FALSE),VLOOKUP($E9&L$8,'HR DELTA'!$K$3:$T$45,3,FALSE))

    Again if this is not what you need further clarification or some examples would be good...

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: If and VLookup formulas in same cell (XP & 2003)

    Nice going Steve, It works well!

    I'm not sure I understand the I9 formula. Would it be possible to explain ?
    There is a lot going on in that formula and I'm not sure how the if statement works
    with the error capture ISNA works with the Vlookup.

    Again many thanks,

    Martin

    PS - GO STEELERS

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
  •