How to make a formula always refer to the last sheet?

Question

In my excel file I have 2 sheets.

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

The second sheet is known as raw data. An example is a column called Fruits.

Apple Apple Apple Banana Banana Pear 

In the first sheet I will have a formula that calculates the amount of time the corresponding fruits appear and the result will be displayed in different cells.

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

I want to do this, is it possible for me to program the formula so that every time I add a new sheet of raw data (3rd sheet), the statistics on the first sheet can refer to the last sheet to get information.

(Assuming that the positioning of the data and all of them coincide with the second sheet.)

What I have done so far is to exit with the GETLASTWSNAME() function, which can always get the name of the last worksheet. but it seems impossible for me to nest a function inside the countif formula itself.

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

The above formula is how I want my formula to work, but unfortunately Excel does not allow me to do this.

Any comments would be appreciated. Thanks!

Answer 1

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

Answer 2

You can use the XLM / Range Name workaround for this rather than VBA if you prefer

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

This formula talks about all sheets, and then returns the last (using COUNTA). The RAND()*0) ensures that this formula is volatile and is updated when Excel does

If you use VBA, you will need to make sure that your GETLASTWSNAME function GETLASTWSNAME unstable, that is, it is updated when changes occur.