Index...

[How_Do_I]

If I'm looking at a single column which starts in B1, can I use a value from another cell i.e A1 = 10 to create an array to Index...

So in this case I want my Index array to be B1 and the 10 cells (A1 value)... B1:B11...

Only formula solutions please... I'm not interested in VBA...

Alternately, how can I extract the following data from this which is a Jeanie of a Pivot Table...

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 141px"><COL style="WIDTH: 58px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Barnsley</TD><TD>DD</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD>DL</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD>DW</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD>LD</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD>LL</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD>LW</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD>WL</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD>WW</TD><TD style="TEXT-ALIGN: right">2</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

 

[How_Do_I]

Doesn't Excel create a GETPIVOTTABLE formula for you that returns the 6? If so what is it?

I read something about this last night... I think it said typing the "=" and highlighting the cell "by-passes" the GETPIVOTTABLE... Let me try and build a formula using Fx or whatever it is to see the code...

Cancel my last, I forgot that I copied the Pivot table for my Jeanie... Sorry, you should know I'm half-baked from our previous dealings!!!

Using the Pivot Table I get:-

=GETPIVOTDATA("HT FT",$A$4,"Home","Barnsley","HT FT","DD")

 

[Andrew Poulsom]

Did you see this?

http://www.contextures.com/xlPivot06.html#GetPivotData

 

[How_Do_I]

In case my edit didn't push the thread up and you don't know I've posted Andrew...

=GETPIVOTDATA("HT FT",$A$4,"Home","Barnsley","HT FT","DD")

 

[Andrew Poulsom]

So that could be:

=GETPIVOTDATA("HT FT",$A$4,"Home",$F2,"HT FT",G$1)

copied down and across. To allow for missing data you can use:

=IF(ISERROR(GETPIVOTDATA("HT FT",$A$4,"Home",$F2,"HT FT",G$1)),"",GETPIVOTDATA("HT FT",$A$4,"Home",$F2,"HT FT",G$1))

 

[How_Do_I]

Sorry I missed that in the excitement of these posts... I'll go and read that and have a play with that formula... Thanks for everything Andrew...

 

[How_Do_I]

So that could be:

=GETPIVOTDATA("HT FT",$A$4,"Home",$F2,"HT FT",G$1)

copied down and across. To allow for missing data you can use:

=IF(ISERROR(GETPIVOTDATA("HT FT",$A$4,"Home",$F2,"HT FT",G$1)),"",GETPIVOTDATA("HT FT",$A$4,"Home",$F2,"HT FT",G$1))

I was just about to say that is so close to working when, I found out why it hadn't worked... there are 9 game outcomes and NOT 8... I missed one out in my extract area so Blackpool wouldn't work...

Thank you so much for that Andrew... that really is going to make a massive dent in my book calculation time... the price, I now need all but a total re-build of my book...

Thanks again, I'm so please with that formula...