referring to a total in a cell that varies each month

[marcia2026]

I have one worksheet which lists all of my cancelled checks with a total at
the end. I want to referrence that total on the Reconcilation worksheet.
How do I reference a cell if that cell address changes each month?

marcia

 

[T. Valko]

So, is this total cell the *last* numeric value in the column? If so, try
this:

With the total cell somewhere in column B:

=LOOKUP(1E100,B:B)

 

[marcia2026]

What is the first reference within the formula? ALso, the outstanding checks
are on one worksheet and yes the total will be the last numeric value in the
column. The place that I want the reference to go is on another worksheet.

 

[Peo Sjoblom]

Just change the B:B to include your sheet name and range so if you want the
last numerical value in Sheet1 column A use


=LOOKUP(1E100,'Sheet1'!A:A)

the first reference is a large number to make sure it will find the last
numerical value. It takes advantage of an old bug in Excel.

--


Regards,


Peo Sjoblom

 

[marcia2026]

Thanks Bunches!!! You guys are great. Couldn't do it without cha.

 

[T. Valko]

=LOOKUP(1E100,B:B)

What is the first reference within the formula?

IE100 (or 1E+100) is scientific notation for a really big number: 1 followed
by 100 zeros. It's a "shorthand" method of expresssing:

10000000000000000... (imagine a string of 100 zeros)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

Since there is a good chance that no number in your range will be >=1E100
the formula returns the *last* numeric value in the referenced range.

If the range of interest is on a different sheet just add the sheet name:

=LOOKUP(1E100,Sheet1!B:B)