How To Reference The Last Entry Of A Column In Multiple Sheets

[Minitman]

Greetings,

I need to get the last entry in a column that is making a running
subtotal. This is taking place on several sheets (all in the same
column).

Is there anyway to enter the sheet name into a cell and have it picked
up as a variable in a formula in another cell?

Any help would be most appreciated.

-Minitman

 

[Aladin Akyurek]

Enter the relevant sheet names on a Overview sheet from A1 on, then...

In B1 enter & copy down:

=LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!A:A"))

 

[Minitman]

Hey Aladin,

This works if I clear every row after the last cell. However, there
are usually a couple of hundred extra formulas below the last cell
that were not used. Short of clearing each sheet of excess rows, is
there another way to capture the last value in a column?

-Minitman

 

[Frank Kabel]

Hi
whah kind of formulas do you have in the other rows?

 

[Aladin Akyurek]

Do these formulas belong to the range of interest? If so, what do they
return - 0's?

 

[Minitman]

Hey Aladin and Frank.

The formula from row 8 is:
=IF(OR(H8>0,H8<0),H8+K7,0)

Column K is the column of interest

With your question, I got to thinking and replaced the last 0 with "":
=IF(OR(H8>0,H8<0),H8+K7,"")


This now works.

Thank you both for that pointer, it really helped.

-Minitman

 

[Harlan Grove]

The formula from row 8 is:
=IF(OR(H8>0,H8<0),H8+K7,0)

....

If K7 == -H8, the condition could be true while the formula returns 0.

Also, replace your condition with the equivalent but simpler & quicker
N(H8)<>0.

 

[Minitman]

Hey Harlan,

Thanks for the simplification - it works great.

-Minitman

 

[Minitman]

Hey Aladin,

I got to thinking and was wondering if this formula could not be used
on my entry sheet instead. The sheets in question are labeled by date
(mmm-yy). I modified your formula to this:

=LOOKUP(9.99999999999999E+307,INDIRECT("'"&B2&"'!K:K"))

B2 is an entry cell where I enter the period of interest by month and
year. This is linked to my print sheet as a date called "Filing
Period".

If I change the format of B2 to TEXT, your formula works great.

The link has a slight problem with the formatting!

I would like the Print sheet to print out the date as mmmm, yyyy.
Instead I am getting the text format as mentioned above.

Is there anyway to insert the custom format (mmm-yy) on the B2
reference in the INDIRECT function so that INDIRECT sees

("'"&(mmm-yy)&"'!K:K")

Any help would be appreciated.

-Minitman

 

[Aladin Akyurek]

If B2 houses a true date and you want it to be read as mmm-yy...

=LOOKUP(9.99999999999999E+307,INDIRECT("'"&TEXT(B2,"mmm-yy")&"'!K:K"))

 

[Minitman]

Hey Aladin,

YES! That is exactly what I am looking for .

Thank you.

-Minitman