Calculations Based On Tabs

[ClarianAutomation]

Hi Guys,

New here and done some searching but as I don't know what to call what I am looking for I struggled to find anything.

What I would like to do is on my first tab (Costings) I have a list of the other tabs that are created when pricing up work. On these other tabs in exactly the same cells are Cost Price and Selling Price, I would like to automatically show this value on the front sheet. I have been using a simple ='Tab1'!D6' to gather the cell from that tab in cell D6. This works ok but some of my jobs have many tabs and therefore I have to type out each formula as/when the tabs is generated.

Is there a way (Macro or VBA) to automatically populate this row when a new tab is created using both the data in Cell D6 and the tab name?

i.e
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Existing Product[/TD]
[TD]='ExistingTab'!D6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Product[/TD]
[TD]='NewTab'!D6[/TD]
[TD]<-Populated Automatically[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD]Sum Of Column[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Kind Regards,

Ian

 

[James006]

In order to get a dynamic list of all you sheet names ... see this link :

https://www.extendoffice.com/documents/excel/2653-excel-dynamic-list-of-worksheet-names.html#a1

HTH

 

[ClarianAutomation]

That is brilliant, it does exactly what I was looking for, great site too for all those little formulas you may need.

Many thanks.

Ian

 

[James006]

You are welcome ...