3-D formula

Resolved · High Priority · Version 2013

Fri 15 May 2015, 13:01 Delegate Rob said...

Rob has attended:
Excel Intermediate course

3-D formula

Actually did training on 2010. Trying to create a link from a summary sheet list of names to multiple sheets, all same cell, all sheets same format ='johnsmith':'marysmth' and failing. Diligently following handbook. I don't want to total data, just summarise it.

Mon 18 May 2015, 09:38 Trainer Richard said...

RE: 3-D formula

Hi Rob

Thank you for using the forum to ask a question.

If you are diligently following the Manual I suspect that the result you want may not be achievable using a 3D formula.

The 3D formula is designed to Total or Average the same cell across multiple worksheets and calculate the Answer on the Summary Page. This formula works for Numbers/Values not Text.

What do you wish to achieve:

Are you trying to copy the Names from the Master list on your Summary Sheet and have them on all of the other sheets?
or
Are you trying to combine the Names from Multiple Sheets into one master List on the Summary Sheet?

You can't use use a 3D formula for either of these options.

To copy the Master list of names to all sheets you would need a formula on each sheet that points back to the Summary.
=Summary!A1 or the Cell reference for the name

To Combine Names from Multiple sheets into the Summary you need to use the Consolidate function.

Consolidating is on Pg23 of the Intermediate Manual

The above may help to answer your question. Please come back to me if you decide that it is definitely a 3D formula that you need. I will then talk you through each step.

Kind regards

Richard Bailey
Microsoft Office Specialist Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 18 May 2015, 14:20 Delegate Rob said...

RE: 3-D formula

Dear Richard

This isn't actually what I want, but reading it through is immensely helpful. I've learned something else.

I have cover sheet with 40 odd names in a list. I have data in a single cell, the same cell in 40 tabs for each name. By clicking on the appropriate cell on the cover sheet and then the appropriate tab then cell, the data appears on the cover sheet. I had to do this 40 times because I couldn't select a range of tabs (by wrongly following 3-D formula notes).

This is records of teachers' hours spent teaching or marking exams. One of the things I find hard is adapting business-related examples to my educational situation. Getting there though. Thank you for you quick response.

Mon 18 May 2015, 15:09 Trainer Richard said...

RE: 3-D formula

Hi Rob,

Now I understand what you were trying to achieve.
You wanted a Master list created from the "Name" cell at the top of every sheet.

Clicking 40 times is possibly the fastest way to create what you wanted. There isn't an easy work around.

I appreciate it is hard to relate the Excel skill to your situation. At least in this case, there wasn't a faster option. Glad you are getting there though.

If you have any other questions, we will do our best to help you.

Kind regards

Richard Bailey
Microsoft Office Specialist Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 18 May 2015, 16:01 Delegate Rob said...

RE: 3-D formula

Hi Richard,
Thank you for this. It's not actually the name in the master sheet, but the data totaled up in H49 of each teachers' tab I wanted to jump across to the master. Anyway, all sorted.
Thank you,
Rob

Training courses