excel - How do I get my formula to always reference to the last sheet?

Question

I currently have 2 worksheets in my excel file.

The first sheet is known as the Summary page, which displays an summary result of the second sheet.

The second sheet is known as the raw data. An example would be a column named Fruits.

Apple
Apple
Apple
Banana
Banana
Pear

In the first sheet, I would have a formula that counts the number of time the respective fruits appear and the result will be displayed in different cells.

=COUNTIF(Fruits!A2:A7,"Apple")
=COUNTIF(Fruits!A2:A7,"Banana")

What I wanna do is, is it possible for me to program the formula such that everytime I add a new sheet of raw data (3rd sheet), the statistics on the first sheet is able to reference to the latest sheet to get the information.

(Assuming that the positioning of the data and all are the same as the second sheet.)

What I have done so far is to come out with a function GETLASTWSNAME() which is able to always retrieve the name of the last worksheet. but it seems kinda impossible for me to nest the function within the countif formula itself.

=COUNTIF((GETLASTWSNAME())!A2:A7,"Apple)

The above formula is how i want my formula to work, but sadly excel does not allow me to do that.

Any comments would be appreciated. Thanks!

Answer 1

You can use the XLM/Range Name workaround for this and not VBA if you prefer

1. Define a range name, wshNames to hold the array of sheet names
   =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
   Uses David Hager's technique
2. Use this Excel formula to extract the last sheet name from the array of sheet names
   =INDEX(wshNames,COUNTA(wshNames)+RAND()*0)

This formula says look at all the sheets, then return the last (using the COUNTA). The RAND()*0) portion ensures that the formula is volatile and updates when Excel does

If you do use VBA you will need to ensure your GETLASTWSNAME function is volatile, i.e. it gets updated when changes occur.