How to define a cell name and reference it in a differen sheet

**shaunsul** · 04-25-2011, 06:56 PM

Hello all
I am attempting to use the Define Name feature in Excel 2007 to references a cell that I use to total a column of data, so that I can pull the information from that cell into a different sheet and use it in a chart. Can anyone help with this, I will keep looking but perhaps I am not phrasing my question in a google search correctly.

Also note that the cell that gets the total of information changes with each new data pull, for example one day it may be J350 and the next day it may be J389.

Thanks
Shaun

**Cutter** · 04-25-2011, 07:06 PM

You MAY be able to use a Dynamic Name for the cell.

See if this link helps:
http://www.contextures.com/xlnames01.html

If not upload a small sample file (with no private data) to illustrate what you're doing.

**nimrod** · 04-25-2011, 07:59 PM

In this example the named range is called "TheValue"

Public Sub Demo()
   Range("J350").Value = Range("TheValue").Value
End Sub

**shaunsul** · 04-26-2011, 01:01 PM

Originally Posted by Cutter

You MAY be able to use a Dynamic Name for the cell.

See if this link helps:
http://www.contextures.com/xlnames01.html

If not upload a small sample file (with no private data) to illustrate what you're doing.

Cutter,
I think I get the idea, however, the data I am trying to pull into another sheet is from a column sum of minutes testing is run. The longer testing goes the more minutes will be recorded thus changing the cell where the total minutes accumulated is varies each time I pull the data via a macro.

Attached is a copy of the file, it has no sensitive data in it

**nimrod** · 04-26-2011, 01:27 PM

This line of code would directly sum your hours ... in this demo the hours are in column B

SumOfHours = Application.WorksheetFunction.Sum(Range("B:B"))