Menu
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!
750
Re: Reference cell in previous sheet Alternatively, type '=' in the cell where you want the linked value, then click on the previoussheet tab and select the cell with the required value and hit enter.
To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address. For example, to refer to cell A1 in Sheet2, you type Sheet2!
If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.
You can use the XLM/Range Name workaround for this and not VBA if you prefer
=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) =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.
187
=COUNTIF(INDIRECT(GETLASTWSNAME() & "!A2:A7"),"Apple")
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
