Returning a cell value

[Irwell1878]

I'll explain this as best I can...

On tab 2 I have a table with four column headings within each column there is a highest amount and lowest amount that can change as each week passes and more data is added.

On tab 1, for each column I have in tab 2 I want a cell that updates automatically to show me the highest and the lowest.

Any more information needed just ask.

Thanks.

 

[Andrew Poulsom]

Examples:

=MAX(Sheet2!A:A)
=MIN(Sheet2!A:A)

copied across.

 

[Irwell1878]

Examples:

=MAX(Sheet2!A:A)
=MIN(Sheet2!A:A)

copied across.

I can't wait to get back using Excel on a daily basis, I've forgotten so much basic stuff it's unreal. Love this forum for reminding. Thanks mate.

Quick follow up question.

The four columns in tab 2 are b,c,d,e for example but column A is the ID number, so whilst I want the highest for each column returning I want another cell that also returns the ID number that matches that, can you remind me how to do that?

Thank you.

 

[Andrew Poulsom]

Like this?

=INDEX(Sheet2!$A:$A,MATCH(MAX(Sheet2!B:B),Sheet2!B:B,FALSE))
=INDEX(Sheet2!$A:$A,MATCH(MIN(Sheet2!B:B),Sheet2!B:B,FALSE))

 

[Irwell1878]

Like this?

=INDEX(Sheet2!$A:$A,MATCH(MAX(Sheet2!B:B),Sheet2!B:B,FALSE))
=INDEX(Sheet2!$A:$A,MATCH(MIN(Sheet2!B:B),Sheet2!B:B,FALSE))

Hmmm, don't think this works.


Does this work as a mocked up spreadsheet....

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>
1</SPAN>	Sheet 1</SPAN>		Sheet 2</SPAN>
2</SPAN>
3</SPAN>		ABC</SPAN>	BCD</SPAN>		Week</SPAN>	ABC Values</SPAN>	BCD Values</SPAN>
4</SPAN>		Value</SPAN>	Week</SPAN>	Value</SPAN>	Week</SPAN>		1</SPAN>	15000</SPAN>	15</SPAN>
5</SPAN>	Highest</SPAN>						2</SPAN>	12000</SPAN>	17</SPAN>
6</SPAN>	</SPAN>						3</SPAN>	11000</SPAN>	16</SPAN>
7</SPAN>							4</SPAN>	8000</SPAN>	98</SPAN>
8</SPAN>							5</SPAN>	17000</SPAN>	21</SPAN>
9</SPAN>							6</SPAN>	19900</SPAN>	3</SPAN>
10</SPAN>							7</SPAN>	21000</SPAN>	88</SPAN>
11</SPAN>
12</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=8><COL span=2></COLGROUP>

So B5 should read 21,000 and C5 should be "7"
So D5 should read 98 and E5 should be "4"

 

[Andrew Poulsom]

Assuming your data on Sheet2 is in columns A:C:

B5 =MAX(Sheet2!B:B)
C5 =INDEX(Sheet2!A:A,MATCH(B5,Sheet2!B:B,FALSE))
D5 =MAX(Sheet2!C:C)
E5 =INDEX(Sheet2!A:A,MATCH(D5,Sheet2!C:C,FALSE))

 

[Irwell1878]

Assuming your data on Sheet2 is in columns A:C:

B5 =MAX(Sheet2!B:B)
C5 =INDEX(Sheet2!A:A,MATCH(B5,Sheet2!B:B,FALSE))
D5 =MAX(Sheet2!C:C)
E5 =INDEX(Sheet2!A:A,MATCH(D5,Sheet2!C:C,FALSE))

there is the one, nice one mate. Thank you.