To Mark W. Almost.....The saga continues.....

Posted by Tezza on August 21, 2001 9:34 AM

I put in the formula you suggested and it nearly worked. It gave me the figure for the total fuel at the top of the column, i.e. the total fuel on the first of January. I tried naming my table TOTALFUEL the opposite way round. i.e. $AI$400:$A$8 as opposed to $A$8:$AI$400, but it made no difference. Also I changed my Cell reference from A7 to A8 in your formula as A7 has all the column headings and it was returning an error using A7. Also in the function wizard it says that the Col_index_num is volatile, is this a problem???? Thankyou for the help any further assistance would be gratefully received. Tezza

Posted by Mark W. on August 21, 2001 9:49 AM

Tezza, it would be easier to follow this discussion
if you'd post replies to the original thread
instead of starting a new one.

Anyway... In you earlier posting you described your
worksheet as "...a horizontal sheet with the date
going down column A". I made the bold assumption
that you were describing the TOTALFUEL cell range,
and; therefore, the leftmost column of TOTALFUEL
was a date with fuel readings in columns to the
right of the date. I also presumed that A7
contained a datevalue that could be found in the
leftmost column of TOTALFUEL. In fact, your
attempted HLOOKUP function with its arguments of
A7 and TOTALFUEL reinforced this conclusion. Is
that not the case? If it isn't please describe
the contents of A7 and TOTALFUEL more fully.

Posted by Tezza on August 21, 2001 10:51 AM

Re: Apologies....... Perhaps this might clear up any misunderstanding

I apologise for my ignorance I'm not used to using this form of communication.

Your assumptions were correct, the A7 part was my faupar. The row 7 contains the headings for the various fuel tanks, but the figures and the dates bigin in row 8. The dates have been typed in then copied down column A until the end of the year, and the columns go along with the names of the tanks until column AI which contains the total fuel remaining onboard. The panes have been frozen to allow the headings to remain when you scroll down to the current date. The table TOTALFUEL, is the range of cells from A8:AI400.

I hope this clears up any uncertenties I may have communicated.

Tezza.

Posted by Mark W. on August 21, 2001 11:49 AM

Re: Apologies....... Perhaps this might clear up any misunderstanding

So, if you want to lookup the latest reading for
today use...

=VLOOKUP(TODAY(),TOTALFUEL,MATCH(9.9999999999999E+307,OFFSET(INDEX(TOTALFUEL,1,0),TODAY()-INDEX(TOTALFUEL,1,1),)))

Posted by Tezza on August 21, 2001 2:08 PM

Re: Apologies....... Perhaps this might clear up any misunderstanding

Thanks Mark That worked a treat. There's just one small problem if I might dare to make one small complaint, (Please don't get too P'ed off with me!?!?). As it's a 24hr operation, it's sometimes the case that people look up my spreadsheet after midnight, and as the figures don't get entered into it until midday then it will look up the wrong cell and throw out all the links I make to the other sheet's. That's why I didn't use the TODAY() function, and was trying to get it to go to the last entry in the column by counting them and then using that as the row number. If you are totally fed up with me then I understand and appreciate the help you have given, I'll just need to put a warning on it that the figures will be wrong if looked at after midnight. Tezza So, if you want to lookup the latest reading for

Posted by Mark W. on August 21, 2001 3:03 PM

Okay... give this a whirl!

Posted by Tezza on August 22, 2001 2:31 AM

Re: Okay... give this a whirl!

Hi Mark, I had to give up last night and go to bed, I was starting to go cross eyed!!!! I tried that formula, but it justreturned a zero??? I don't know what it could be as you've totally lost me with the last couple of furmula you've given me!?!?!? Any chance of a quick explanation???? My understanding of excel functions is quite basic and I've only started delving into it doing this project. Thanks again, Tezza =INDEX(TOTALFUEL,MATCH(9.99999999999999E+307,INDEX(TOTALFUEL,0,2)),MATCH(9.99999999999999E+307,OFFSET(INDEX(TOTALFUEL,1,0),MATCH(9.99999999999999E+307,INDEX(TOTALFUEL,0,2))-1,))) : Thanks Mark That worked a treat. There's just one small problem if I might dare to make one small complaint, (Please don't get too P'ed off with me!?!?). As it's a 24hr operation, it's sometimes the case that people look up my spreadsheet after midnight, and as the figures don't get entered into it until midday then it will look up the wrong cell and throw out all the links I make to the other sheet's. That's why I didn't use the TODAY() function, and was trying to get it to go to the last entry in the column by counting them and then using that as the row number. If you are totally fed up with me then I understand and appreciate the help you have given, I'll just need to put a warning on it that the figures will be wrong if looked at after midnight. Tezza

Posted by Mark W. on August 22, 2001 7:57 AM

Re: Okay... give this a whirl!

Okay, but 1st let's review what I believe to be your
worksheet configuration. The defined name, TOTALFUEL,
should refer to cells $A$8:$AI404. And, column A
contains your date values. Column B contains the
1st fuel reading (when recorded) for each date.
So, for discussion purposes let's assume that
A8:A11 contains the following dates: 8/19/01, 8/20/01,
8/21/01, 8/22/01. Furthermore, let's assume that
B8:F11 contains sample data consisting of...

{10,20,30,"",""
;19,23,25,35,""
;8,9,13,"",""
;"","","","",""}

Note: I'm using the null string, "", to indicate
where there are blank cells.

As you can see that row 11 has no fuel readings.
My function will return the value, 13, because that
is the last fuel reading for any date that has at
least one reading in column B. You'll notice that
I use MATCH(9.99999999999999E+307,...) several
times in this formula. These MATCH function calls
are used to find the last numeric value in a given
column or row.

MATCH(9.99999999999999E+307,INDEX(TOTALFUEL,0,2))
finds the last row that has a value in column B. It
identifes the 3rd row in the TOTALFUEL range -- row
10 on the worksheet -- because it finds the value,
8.

I use MATCH(9.99999999999999E+307,INDEX(TOTALFUEL,0,2))-1
with an OFFSET function to return the entire set
of values found on this last row...

{37124,8,9,13,""} where 37124 is the date value for
8/21/01.

So, we're left with something like this...

=INDEX(TOTALFUEL,3,MATCH(9.99999999999999E+307,{37124,8,9,13,""}))

This last MATCH function returns the column number, 4.
That's where the value, 13, is located. So, now
we have...

=INDEX(TOTALFUEL,3,4)

...which returns the TOTALFUEL value found at the
intersection of the 3rd row and 4th column, 13.
Voila!!

Posted by Mark W. on August 22, 2001 8:09 AM

Oops! A Typo...

The defined name, TOTALFUEL, should refer to cells $A$8:$AI$400. Okay, but 1st let's review what I believe to be your

Posted by Tezza on August 22, 2001 4:15 PM

Hi Mark, I've finally............

Got it through my thick skull!!!!! The reason it was returning zero was because I've been buggering about so much with the sheet that there was a figure in column B, but nothing else in the rest of the row. So yep you guessed it, the total at the end was in fact zero!!!!!!!! DUHHHHHH!!!! Thanks for the explanation, once I read that it clicked straight away. I'll stop pestering you now and let you answer some of the more intelligent population :-)) Thanks again, you've finally laid my demon to rest, Cheers Tezza :-))) The defined name, TOTALFUEL, should refer to cells $A$8:$AI$400. : Okay, but 1st let's review what I believe to be your