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

x
Solved

# Excel Formula

Posted on 2011-04-29
Medium Priority
353 Views
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
Question by:starport
• 2
• 2

LVL 33

Accepted Solution

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

ID: 35493535
0

LVL 43

Assisted Solution

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

ID: 35493578
See attached sample
0

Author Closing Comment

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

## Featured Post

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.