Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Variable results (2002)

    I'm trying to sort out a way to be able to update data based on a variable criteria. The attached spread sheet explains it all. I'm sure this is faily simple for some expert out there. Merry Christmas and a safe, prosperous and happy 2004.

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    Are the formulas I put into your workbook what you are looking for?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Variable results (2002)

    Legare - they are just what I needed!!

    Thankyou very much. Have a great 2004!!

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23> Dean

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Variable results (2002)

    Legare,

    I plugged the formula into my working spread sheet and that identified a bit of a problem - in the working spreadsheet the data goes out to AD and there are a number of hidden colums which makes if hard to know the col_index_num. Is there some other method which will let me drag the formula across rather than have to renumber the col_index_num manually?

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Dean

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variable results (2002)

    Try the following variation on Legare's formula. It picks up the column index by looking at the month in the cell above. The example is for cell C9

    =VLOOKUP($B$8,'Hours Data'!$C$3:$O$8,MONTH(D$8)+1,FALSE)

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    Actually, I think the following will give you want you want as previous formulas did not include your entire range. (Telfer was not included)

    =VLOOKUP($B$8,'Hours Data'!$C$3:$O$9,MONTH(D$8)+1,FALSE)

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Variable results (2002)

    Guys - thanks for the new formula. However in the working spreadsheet the data runs over 3 years. The formula does not differentiate the years. Any thoughts?

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    Assuming that your table on the "Hours Data" sheet just continues out to AM9, then try the following formula:

    <pre>=VLOOKUP($B$8,'Hours Data'!$C$3:$AM$9,MONTH(D$8)+1+(YEAR(D$8)-2004)*12,FALSE)
    </pre>

    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Variable results (2002)

    Thankyou all for all your help!! I'm not sure what I'm doing wrong but the vlookup values don't match the correct values for the month & year. I've attached a spreadsheet that shows the problem. I'm sure its something simple and basic but unfortunately I'm no expert - but I'm working on it!!

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    I'm no wizz with LOOKUP worksheet formulae, but it appears that your VLOOKUP is not accounting for the fact that, for instance, MONTH("1/10/2001") returns 10. This offsets the target column value by this amount. Changing to:

    =VLOOKUP($B$3,hours!$C$3:$AC$5,MONTH(D$4)<font color=red>-9</font color=red>+1+(YEAR(D$4)-2001)*12,FALSE)

    will bring things back into registry, and your calculated values will then equal your "expected" values.

    Alan

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    Alan is correct in his answer since your Hours table starts in the tenth month not the first. The following formula is slightly simpler than his though:

    <pre>=VLOOKUP($B$3,hours!$C$3:$AC$5,MONTH(D$4)-8+(YEAR(D$4)-2001)*12,FALSE)
    </pre>

    Legare Coleman

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    Quite right too, Legare! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I used the <font color=red>-9</font color=red> in my formula to indicate where/ why the original was "out" by 9 columns. Yours is, of course, a much more sensible way to actually enter the formula.

    Alan

  13. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Variable results (2002)

    Thank you for all your help. This works fine! To help me learn and understand the formula =VLOOKUP($B$3,hours!$C$3:$AC$5,MONTH(D$4)-8+(YEAR(D$4)-2001)*12,FALSE). What does the -8,*12 & FALSE actually do? Assuming that the date started in Jan 2004 how would the formula change?

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable results (2002)

    Those numbers are there as "adjustments", for want of a better term, so that the return values of the month and year functions can be used to determine the correct column number on your "Hours Data" sheet. The syntax for VLOOKUP is:
    VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

    and in your case,
    table_array = hours!$C$3:$AC$5
    col_index_num = MONTH(D$4)-8+(YEAR(D$4)-2001)*12

    The way you have layed this sheet out, the data begins in column 4 or D, but this is column 2 of your specified table_array, which begins in column C. The first data item is for October 2001and you want the manipulation of this date to return a 2. MONTH(D$4)-8 is used for this purpose (= 10 - 8). For each subsequent month you want the return value to increment by 1. This works until December, after which the month returns a 1, but you then need the column reference to be 5. This is where the year (difference) manipulation comes into play. (YEAR(D$4)-2001)*12 now becomes 12 and the whole expression
    MONTH(D$4)-8+(YEAR(D$4)-2001)*12 becomes:
    1 -8 + (1) * 12 = 5

    So you continue to get the correct offset for all subsequent month/ year combinations. If you wanted to start in Jan 2004, your formula would be:
    MONTH(D$4)+1+(YEAR(D$4)-2004)*12

    According to the help file:
    "Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned."

    Hope that helps explain it.

    Alan

  15. #15
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Variable results (2002)

    That's great Alan. Thanks very much for your help

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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
  •