Create Formula Based on sheet name

[mcasey6747]

Hi,

I built a summary tab that links to sheets behind it.

My question is, when I create a new sheet, I want it to update to the summary via formula (not VBA if possible).

I have each subsheet pull the sheet name via that formula alot of people use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

But what I'd like to do is on main page type in that sheet name and then be able to add the cell to it.

Example:

Summary page add new sheet called dog_name.

Formula would combine "dog_name"+C7 to combine for =dog_name!C7 to sum C7 of that sheet.

Any idea how to do it? Have a better idea?

Thanks

 

[Z51]

If you want to sum cell C7 in multiple worksheets you can use:

=SUM(Sheet5:Sheet20!C7)

If you insert a sheet between Sheet5 and Sheet20 (physically), the new sheet will be included in the total. If your sheet names have spaces, be sure to wrap them in single quotes, e.g.

=SUM('dog name 1:dog name 2'!C7)

 

[mcasey6747]

Thank you.

What if I create a new sheet 'dog_3' and ONLY want to show that sheet on it's own line.

So Let's say I currently have the following: (not selling dogs or anything just an example )

The below example doesn't total all of them which is what the above formula appears to do. I want to demonstrate just the information from dog_3

The point is let's say I add 20 more sheets with the same layout, how can I do this without having do do a find and replace for each line?

 

[Z51]

Without VBA it isn't exactly straightforward, but this provides a possible solution:

How To Generate A List Of Sheet Names From A Workbook Without VBA | How To Excel

www.howtoexcel.org

Skip the hyperlink section, and instead of =INDEX(SheetNames,A2) you could use =IFERROR(INDEX(SheetNames,A2),"") to hide rows with blanks. Then on the Summary sheet you could enter simply ='Sheet_name_with_list'!B2 and fill that down. The drawback is that it will include all sheets, including the Summary named sheet. After adding new sheets, you would need to fully calculate the workbook to get the Summary sheet to update, e.g. CTRL+SHIFT+ALT+F9.

 

[mcasey6747]

That's interesting thanks.

So that would essentially create a summary of the sheets. Now when I do have that summary back to that screen show let's say total dog names are on C9 in every sheet and Cost per dog is on D17.

How can I combine that printout you mentioned plus the cell number.

In my below example C9 would be the same cell on each sheet. So I want it to pull from C9 on dog_1 for that one and then C9 from dog_2 sheet.

Can I create a formula that will combine those two cell contents into a formula? I'd need to add the exclamation point as well which may screw up the formula. Thoughts?
So therefore when I add dog_3 I can either copy from the summary as you taught or just type dog_3 then pull down the formulas and it'll all pull from the new dog_3 sheet.

I tried for c5 =b5+"!"+C3 but that didn't work too well. Any ideas?

 

[Z51]

This should work...

=INDIRECT("'"&B5&"'!C3")

 

[mcasey6747]

Thank you so much. I now have a spiffy index as well. I really appreciate the help.