1. ## 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. ## Re: Variable results (2002)

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

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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>

9. ## 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. ## 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. ## 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>

12. ## 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. ## 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. ## 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)

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. ## 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 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
•