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]

Hi,

If you need a 3D sum formula ... take a look at following link :

https://www.ablebits.com/office-addins-blog/2015/12/09/excel-3d-reference-formula/

Hope this will help

 

[ClarianAutomation]

Hi,

If you need a 3D sum formula ... take a look at following link :

https://www.ablebits.com/office-addins-blog/2015/12/09/excel-3d-reference-formula/

Hope this will help

You are a start my friend!! This was exactly what I was looking for and I now have it working

Thenk you, very much appreciated.

Regards,

Ian

 

[ClarianAutomation]

This has got me thinking, how do I populate the name in the cell from the tab (or worksheet) name?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Tab 1 (From Worksheet Name)[/TD]
[TD]Value [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tab 2[/TD]
[TD]Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tab 3[/TD]
[TD]Value[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If I subsequently change the tab name it will update the cell with the new name?

Kind Regards,

Ian

 

[James006]

Glad you could fix your problem ...

If you need to have a dynamic formula which adjusts itself whenever the sheets' names are changed ...

you will probably need to use the Indirect() function ...

Would you mind posting your current formula ...

 

[ClarianAutomation]

Thanks for your reply.

My current formula is

=IF(A4="","",INDIRECT("'"& A4 & "'!D6"))

This will populate the data based on the name in the cell at A4, what I am looking at doing is to populate the name in A4 based on the tab name (Name 1, Name 2, Name 3) so that if I change the Tab name it will update the cell.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Selling Price[/TD]
[TD]Cost Price[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]=IF(A4="","",INDIRECT("'"& A4 & "'!D6"))<strike></strike>
[/TD]
[TD]<strike></strike>=IF(A4="","",INDIRECT("'"& A4 & "'!D5"))<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]=IF(A5="","",INDIRECT("'"& A5 & "'!D6"))<strike></strike>
[/TD]
[TD]<strike></strike>

=IF(A5="","",INDIRECT("'"& A5 & "'!D5"))​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]=IF(A6="","",INDIRECT("'"& A6 & "'!D6"))<strike></strike>
[/TD]
[TD]<strike></strike>

=IF(A6="","",INDIRECT("'"& A6 & "'!D5"))​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

I hope that makes sense?

Regards,
Ian

 

[James006]

Thanks for your formulas ...

My question was about your latest 3D sum formula ...

 

[ClarianAutomation]

Thanks for your formulas ...

My question was about your latest 3D sum formula ...

That is all I use, the summation is simply done as a sum of all the cells in that column. The formula quoted above is to retrieve the value from the same cell on each worksheet but I have to enter the worksheet name manually, if I change the worksheet name in the tab I have to change it in cell A# for it to calculate. What I am trying to do is read the name of the worksheet into the cell.

Regards,

Ian

 

[James006]

Sorry misunderstood your question ...

In order to get the sheet name, you can use following formula

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1,255)

Hope this will help

 

[ClarianAutomation]

This only gives the name of the sheet it is located on whereas I am trying to get the name of the other sheets in the workbook. I have a front page called Costings and I am trying to get the costs from the other tabs on Cell D6 and D5 to add up the total selling price and cost price. By being able to read the tab name (sheet name) I can get the first formula to work without having to type the names in manually, and to update when the tab names are changed.

if I put

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1,255) into a cell on the costings page it simply returns the text Costings.

Regards,

Ian
​