Reference a cell in a named range

[JamesMac]

How do you reference a cell within a named range?

 

[oldbrewer]

say the range is 45,55,65

you want to find the value in the second cell in the range ?

 

[Rick Rothstein]

How do you reference a cell within a named range?

Show us an example of the Named Range (which cells comprise it) and with cell within it that you want to reference.

 

[Fluff]

Maybe

Sub chk()
MsgBox Range("myrng").Cells(3, 1).Value
End Sub

 

[JamesMac]

I have multiple events sequenced throughout the day, each with a start time and and an end time. I need to calculate the delay between when one event ended and the next event started.

In the table below, I have named ranges called "StartTime", "EndTime" and "Delay". I need to create a formula that subtracts the end time of event#1 from the start time of Event#2. It's easy if I use Row/column references. But when I use Named ranges, I need to be able to identify a particular cell with a named range.

For example, if I could use a cell name such as "StartTime1" to identify the column as "StartTime" and the row as "1", that would address my problem.

Hope this makes sense.
Thanks for your help.
Jim


Event Start Time End Time Delay

Event#1 10:00 11:00 N/A
Event#2 11:05 11:30 0:05
Event#3 11:45 12:00 0:15

 

[JamesMac]

Sorry, the table didn't display well at all. I'll try pasting a table

Jim


[TABLE="width: 367"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Event[/TD]
[TD] [/TD]
[TD]StarTime[/TD]
[TD] [/TD]
[TD]EndTime[/TD]
[TD] [/TD]
[TD]Delay[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]10:00[/TD]
[TD] [/TD]
[TD]11:00[/TD]
[TD] [/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]11:05[/TD]
[TD] [/TD]
[TD]11:15[/TD]
[TD] [/TD]
[TD]0:05[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]11:30[/TD]
[TD] [/TD]
[TD]12:10[/TD]
[TD] [/TD]
[TD]0:15[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I think I would just use the cell references as the formula is much simpler, but would this suffice if you really want to use the named ranges?
D2 would house a manual "N/A"
D3 copied down.

Excel Workbook
	A	B	C	D
1	Event	StarTime	EndTime	Delay
2	1	10:00	11:00	N/A
3	2	11:05	11:15	0:05
4	3	11:30	12:10	0:15
Delay

 

[JamesMac]

I think I would just use the cell references as the formula is much simpler, but would this suffice if you really want to use the named ranges?
D2 would house a manual "N/A"
D3 copied down.

Delay

	A	B	C	D

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:71px;"><col style="width:70px;"><col style="width:52px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Event[/TD]
[TD="align: right"]StarTime[/TD]
[TD="align: right"]EndTime[/TD]
[TD="align: right"]Delay[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]N/A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11:05[/TD]
[TD="align: right"]11:15[/TD]
[TD="align: right"]0:05[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]12:10[/TD]
[TD="align: right"]0:15[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D3 =INDEX(StartTime,ROWS(D$1:D3)-1)-INDEX(EndTime,ROWS(D$1:D3)-2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi,

I agree, it's not worth using named ranges if that's what's required to reference a specific cell within a range.

Thank you for you help.
Jim