Formula using a named range, where name is in a cell

[AusSteelMan]

Hi everyone,

Problem:
Would like to be able to build a formula where one of the cell reference contains the Name of the range I would like to use in the formula.

Context:
I have a real workbook which has 32 series of values which were collected with a process control system logger at 1 minute intervals for 365 days (yes 525600 rows of data). I need to make some sense of the data. I have a second worksheet were I can extract hourly and daily averages for a selected month (much more manageable chunks of data). I would now like to interogate this data set for certain parameters, such as Max and Min for example.
As the length of the data set varies (days in month, for example), I have named the series dynamically so the length only contains values and no #N/As. I have a strict naming convention for these Names.

I have attached a mock worksheet of the data set which shows the range names and how I would like to use them in the formulae.

Is this possible?
(I realise, while I set this thread up that I could have copied and edited the formulas individually, but this may be useful in the future since I usually have a strict - basically formula derived - naming convention.

Mock_Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 141px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Mock_Sheet1</TD><TD></TD><TD>Max</TD><TD>=max(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">10</TD><TD>#REF!</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD>Min</TD><TD>=min(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD>Average</TD><TD>=average(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">6.58333333</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">Dynamic Range Name</TD><TD style="BACKGROUND-COLOR: #99cc00">rngdyn_Pen1</TD><TD>rngdyn_Pen2</TD><TD>rngdyn_Pen3</TD><TD>rngdyn_Pen4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Data point</TD><TD></TD><TD>Pen</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">9</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">11</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E1</TD><TD>=MAX(E9:E20)</TD></TR><TR><TD>F1</TD><TD>=MAX(INDIRECT(F6))</TD></TR><TR><TD>E2</TD><TD>=MIN(E9:E20)</TD></TR><TR><TD>E3</TD><TD>=AVERAGE(E9:E20)</TD></TR><TR><TD>D6</TD><TD>="rngdyn_"&$C$8&D8</TD></TR><TR><TD>E6</TD><TD>="rngdyn_"&$C$8&E8</TD></TR><TR><TD>F6</TD><TD>="rngdyn_"&$C$8&F8</TD></TR><TR><TD>G6</TD><TD>="rngdyn_"&$C$8&G8</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Note: "Formulae" in D1 to D3 is formatted as Text


I tried INDIRECT and fished around for other solutions, but with no luck (obliviously).

Any help, even confirmation it cannot be done, would be greatly appreciated.

Thanks and regards,
Darren

 

[jkpieterse]

INDIRECT should work.

 

[Jerry Sullivan]

Hi Darren,

In this thread, RichardSchollar (MrExcel MVP and Moderator) shows how to create a Name with the formula using EVALUATE and INDIRECT that will allow you to reference your DNR.

http://www.accountingweb.co.uk/anyanswers/dynamic-named-ranges-and-indirect-function

For your example you could create a Name called DynRef defined as:
=EVALUATE(INDIRECT("D6"))

Then you could use it in formulas as...
=MAX(DynRef )

 

[mariner_96]

i m getting error if i take value for vlookup, if vale is divided by 1000 ,vlookup formula is not working,,

 

[Jerry Sullivan]

i m getting error if i take value for vlookup, if vale is divided by 1000 ,vlookup formula is not working,,

Hello mariner,
Is your comment related to the question originally asked by AusSteelMan?

If it's a new question, you'll have better results if you start a new thread.

 

[mariner_96]

where is new thread . i cant find

 

[Jerry Sullivan]

where is new thread . i cant find

To start a New Thread, click on this link:
http://www.mrexcel.com/forum/forumdisplay.php?f=10

Then click on New Thread (It's in the the upper left part of the page)

Before posting, please review the Posting guidelines, forum rules and FAQs - this will improve your chances of getting the help you want.
(links to these pages are in my signature below).

 

[AusSteelMan]

Hi again.

Firstly, apologies for the delayed response to your help, I was off work for medical reasons Wed to Fri last week.

So here is what I have found

1. jkpieterse: INDIRECT did not work as expected. The first reason is that I made an error in my DNR, so the INDIRECT had nothing to look up. However, when I fixed this error, it still did not work. I decided to simplify things and directly name a range and then INDIRECT worked. See Col G below (for "Pen4"). So the only difference between Pen3 and Pen4 is that Pen3 is a dynamic range and Pen4 is a fixed range. Your confidence that it should work kept me trying INDIRECT until I got a good response.

So, does this make sense to you? Would the DNR behave differently to the regular NR?


2. Thanks JS411: That link to RichardSchollar's post worked well.

This now means I can copy the formula across all columns and it works well.

I will have to do a bit more research on how/why EVALUATE works.


Here is the sheet, followed by the named ranges (from Name Manager +)

Thankyou both (& Richard) for your help.

Mock_Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 141px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Mock_Sheet1</TD><TD></TD><TD>Max</TD><TD>=max(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">10</TD><TD>#REF!</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD>Min</TD><TD>=min(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">2</TD><TD>#REF!</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD>Average</TD><TD>=average(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">6.58333333</TD><TD>#REF!</TD><TD style="TEXT-ALIGN: right">4.75</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">4.5</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">Dynamic Range Name</TD><TD style="BACKGROUND-COLOR: #99cc00">rngdyn_Pen1</TD><TD>rngdyn_Pen2</TD><TD>rngdyn_Pen3</TD><TD>rng_Pen4</TD><TD>rngdyn_Pen5</TD><TD>rngdyn_Pen6</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Data point</TD><TD></TD><TD>Pen</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">7</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">9</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">6</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">11</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">8</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E1</TD><TD>=MAX(E9:E20)</TD></TR><TR><TD>F1</TD><TD>=MAX(INDIRECT(F$6))</TD></TR><TR><TD>G1</TD><TD>=MAX(INDIRECT(G$6))</TD></TR><TR><TD>H1</TD><TD>=MAX(MyDynRng_Max)</TD></TR><TR><TD>I1</TD><TD>=MAX(MyDynRng_Max)</TD></TR><TR><TD>E2</TD><TD>=MIN(E9:E20)</TD></TR><TR><TD>F2</TD><TD>=MIN(INDIRECT(F$6))</TD></TR><TR><TD>G2</TD><TD>=MIN(INDIRECT(G$6))</TD></TR><TR><TD>H2</TD><TD>=MIN(MyDynRng_Min)</TD></TR><TR><TD>I2</TD><TD>=MIN(MyDynRng_Min)</TD></TR><TR><TD>E3</TD><TD>=AVERAGE(E9:E20)</TD></TR><TR><TD>F3</TD><TD>=AVERAGE(INDIRECT(F$6))</TD></TR><TR><TD>G3</TD><TD>=AVERAGE(INDIRECT(G$6))</TD></TR><TR><TD>H3</TD><TD>=AVERAGE(MyDynRng_Average)</TD></TR><TR><TD>I3</TD><TD>=AVERAGE(MyDynRng_Average)</TD></TR><TR><TD>D6</TD><TD>="rngdyn_"&$C$8&D8</TD></TR><TR><TD>E6</TD><TD>="rngdyn_"&$C$8&E8</TD></TR><TR><TD>F6</TD><TD>="rngdyn_"&$C$8&F8</TD></TR><TR><TD>G6</TD><TD>="rng_"&$C$8&G8</TD></TR><TR><TD>H6</TD><TD>="rngdyn_"&$C$8&H8</TD></TR><TR><TD>I6</TD><TD>="rngdyn_"&$C$8&I8</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Names 2011-Nov-21

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 119px"><COL style="WIDTH: 334px"><COL style="WIDTH: 48px"><COL style="WIDTH: 77px"><COL style="WIDTH: 45px"><COL style="WIDTH: 82px"><COL style="WIDTH: 56px"><COL style="WIDTH: 45px"><COL style="WIDTH: 42px"><COL style="WIDTH: 38px"><COL style="WIDTH: 77px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">RefersToLocal</TD><TD style="FONT-WEIGHT: bold">Visible</TD><TD style="FONT-WEIGHT: bold">Local name</TD><TD style="FONT-WEIGHT: bold">Error</TD><TD style="FONT-WEIGHT: bold">External link</TD><TD style="FONT-WEIGHT: bold">Relative</TD><TD style="FONT-WEIGHT: bold">3-D</TD><TD style="FONT-WEIGHT: bold">Areas</TD><TD style="FONT-WEIGHT: bold">Cells</TD><TD style="FONT-WEIGHT: bold">UnUsed</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>MyDynRng_Average</TD><TD>=EVALUATE(INDIRECT("R[3]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>MyDynRng_Max</TD><TD>=EVALUATE(INDIRECT("R[5]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>MyDynRng_Min</TD><TD>=EVALUATE(INDIRECT("R[4]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>rng_Pen4</TD><TD>=Mock_Sheet1!$G$9:$G$20</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>rngdyn_Pen3</TD><TD>=OFFSET(Mock_Sheet1!$F$8,1,0,COUNT(Mock_Sheet1!$F$9:$F$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>rngdyn_Pen5</TD><TD>=OFFSET(Mock_Sheet1!$H$8,1,0,COUNT(Mock_Sheet1!$H$9:$H$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>rngdyn_Pen6</TD><TD>=OFFSET(Mock_Sheet1!$I$8,1,0,COUNT(Mock_Sheet1!$I$9:$I$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>rngdyn_Pen7</TD><TD>=OFFSET(Mock_Sheet1!$J$8,1,0,COUNT(Mock_Sheet1!$J$9:$J$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>rngdyn_Pen8</TD><TD>=OFFSET(Mock_Sheet1!$K$8,1,0,COUNT(Mock_Sheet1!$K$9:$K$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD>Not checked</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[jkpieterse]

I'm sorry, I forgot about INDIRECT not working with dynamic range names. Can you convert your table to a list (table in 2007)? If so, range names inside the list/table are dynamic by default.

 

[mikerickson]

Create Name: indicatedRange
RefersTo: =EVALUATE(REPT(Sheet1!$C$1,1+NOW()-NOW()))

=MAX(rngdyn_Pen1) becomes =MAX(indicatedRange)


The NOW()-NOW() is to make it volatile so it updates dynamic ranges.