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

 

[Andrew Poulsom]

You can use:

B1:INDEX(B:B,A1)

to construct your range.

 

[schielrn]

I'm not really sure what you are asking, but maybe you want to use the offset formula to set the height of a range?

See below as the first formula is using 10 in A1 to define the range size and you get back a value and the 2nd formula is using 3 in A2 and it doesn't set the range size large enough to find the value:

Excel Workbook
	A	B	C	D
1	10	DD		LL
2	3	DL		#N/A
3		DW
4		LD
5		LL
6		LW
7		WL
8		WW
Sheet1

Cell Formulas
Range	Formula
D1	=INDEX(B1:B8,MATCH("LL",OFFSET(B1,0,0,A1),0))
D2	=INDEX(B1:B8,MATCH("LL",OFFSET(B1,0,0,A2),0))

Hope that helps.

 

[How_Do_I]

You can use:

B1:INDEX(B:B,A1)

to construct your range.

Thanks Andrew

 

[How_Do_I]

I'm not really sure what you are asking,

Interestingly enough, nor am I anymore...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've just about finish my project, I was in the process of tidying a few things up, asking the odd xl question here and there, generally minding my own business and then somebody (thanks Fazza!) introduced me to Pivot Tables...!<o></o>
<o></o>
Now I don't know my a$£e from my elbow regarding which direction I'm heading in...<o></o>
<o></o>
I know Pivot Tables do my calculations really quickly, I know that most of my formulas can be replaced by Pivot Tables but, I want to take the data from the Pivot Tables and use the visual methods I've built into my book to display everything... That might seem silly and may come across as defeating the object of the pivot tables but, I can't see a way of getting a league table sorted by points, goal difference and goals scored from Pivot Tables alone...<o></o>
<o> </o>
So I’ve looked into VBA refreshing pivot tables by book / sheet events… I don’t want VBA to copy ranges as I don’t really understand or indeed like VBA… So I’m asking small scale questions to see if I can extract data from Pivot Tables… I think!!!<o></o>

 

[Andrew Poulsom]

Check out the GETPIVOTDATA function in Excel Help.

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

 

[How_Do_I]

Check out the GETPIVOTDATA function in Excel Help.

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

I was just about to post when I found your comment Andew... I've seen that, I can get the data out... It's getting it into a workable layout that I can't work out... Even if I type = in a cell and then click and drag to get a copy of the pivot table my problem is working with it...

I'll post what I had...

If A1:C17 are the results of a pivot table, how would I take that data and fill in the highlighted area please… The problem being… for “Barnsley” I’ll need to use ((Match on Blackpool)-(Match on <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Barnsley</st1lace>))-1) to find out how many double lettered codes there are… There can be a maximum of 8 but could be fewer and that formula then needs to be applied to all the teams… Does that make sense or am I missing something?

Sheet1


<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: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></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><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Barnsley</TD><TD>DD</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD><TD> </TD><TD> </TD><TD>DD</TD><TD>DL</TD><TD>DW</TD><TD>LD</TD><TD>LL</TD><TD>LW</TD><TD>WL</TD><TD>WW</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD>DL</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD>Barnsley</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD>DW</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD><TD>Blackpool</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD>LD</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD>LL</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD>LW</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD>WL</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD>WW</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Barnsley Total</TD><TD> </TD><TD style="TEXT-ALIGN: right">23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Blackpool</TD><TD>DD</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD>DL</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD>DW</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD>LD</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD>LL</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD>WD</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD>WW</TD><TD style="TEXT-ALIGN: right">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Blackpool Total</TD><TD> </TD><TD style="TEXT-ALIGN: right">23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


If the Pivot Table put "Barnsley" in the 8 rows (A1:A8) I could do this...

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

 

[Andrew Poulsom]

Can't you refer to the cells in column F and row 1? If you select G2, type = and point to the value next to Barnsley DD what GETPIVOTTABLE formula does Excel give you?

 

[How_Do_I]

Can't you refer to the cells in column F and row 1? If you select G2, type = and point to the value next to Barnsley DD what GETPIVOTTABLE formula does Excel give you?

Yes, I get the 6...

I'm not sure if I'm missing something or complicating this but please bear with me...

DD = Draw/Draw... Draw at half time, draw at full time but filling the highlighted area by hand can't be the way... and what happens if the first game of the season isn't DD... that code say WW would be there...

If the Pivot table put those game codes in the same order and placed a 0 in the games that haven't had that outcome to date then yes, I'd fill it by hand... Note that Barnsley had all 8 game outcomes yet Blackpool in their 23 home games only had 7...

Of course when dealing with my team next season I'm only going to get 19 WW...! Come on you Blues...!!!

 

[Andrew Poulsom]

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