[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Formula

Posted on 2011-04-29
5
Medium Priority
?
353 Views
Last Modified: 2012-05-11
I have a multi tab spreadsheet with a first tab which is a summary tab.  
To illustrate what I'm trying to do consider the following:

If the tabs are named RED, WHITE, BLUE, and on the SUMMARY tab I want to have a formula that says : =RED!A1+WHITE!A1+BLUE!A1  which in Cell A1 of  the SUMMARY tab reports the result.  Here's the qustion: is there a way on the summary tab that I can reference the names RED WHITE and BLUE (as words located in 3 separate cells on the SUMMARY spreadsheet) so that I can achieve teh same outcome as the formula listed, but referencess these 3 cells on the Summary sheet containing the names RED, WHITE and BLUE - like "injecting" the tab name into the formula.  Thanks in advance
0
Comment
Question by:starport
  • 2
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 800 total points
ID: 35493528
No but you could name your A1 cells. On RED tab cell A1, you can create a named range like RED; on your cell A1 of tab WHITE you can create a named range with the name WHITE, etc... Then on a cell you can do =RED+WHITE+BLUE that will sum the values of cell A1 from each tab.

Please take a look at the attached example.

jppinto
Named-Cells.xlsx
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35493535
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 200 total points
ID: 35493559
=indirect(A1&"!a1")

will give the value in A1 of the sheet whose name is in A1 of the current sheet.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35493578
See attached sample
Indirect-addressing.xls
0
 

Author Closing Comment

by:starport
ID: 35494751
Exactly what I Needed, thanks so much
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question