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

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)

--
Biff
Microsoft Excel MVP


"marcia2026" wrote in message
...
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

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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"marcia2026" wrote in message
...
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

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" wrote in message
...
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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"marcia2026" wrote in message
...
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

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

"Peo Sjoblom" wrote:

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" wrote in message
...
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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"marcia2026" wrote in message
...
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

=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)


--
Biff
Microsoft Excel MVP


"marcia2026" wrote in message
...
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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"marcia2026" wrote in message
...
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