Find the cell location of a result from a formula

[fiachra]

I have a simple max formula in Sheet1 that finds the max value from ranges of values in multiple other sheets.

i.e. =MAX('Sheet 2'!A1:P44,'Sheet 3'!A1:P44,'Sheet 4'!A1:P44)

I would like to show the location of that max value in the cell next to the value on Sheet 1, including both worksheet name and cell reference.

I have been trying to use match and index but it has not worked due to the multiple columns.

Any help would be greatly appreciated.

 

[oldbrewer]

use FIND to tell you which sheet it is in and then MATCH for each column ??

 

[fiachra]

Hi oldbrewer,

How exactly would that find value work. lets say sheet 3 contained the max value of 1254. Therefore my formula on sheet 1 would equal 1254. Could you explain how the find function would return the correct sheet for that value?

Thank you for the quick response!

 

[oldbrewer]

						mytable	99
						secondtable	100
	automatically detect which table has max value	secondtable	100	row	4	column	3
							mytable
						0	0	0	0	0
				1	0	1	4	8	9	44
				2	0	4	11	39	4	11
				3	0	6	23	84	6	23
				4	0	8	34	99	8	34
				5	0	9	45	6	9	45
	detects row with max value>>>>>>>>>>>>	#N/A							#N/A	0
	detects column with max value>>>>>>>>>>	0							0	0
							secondtable							4
															3
						0	0	1	0	0
				1	0	1	4	8	9	44
				2	0	4	11	39	4	11
				3	0	6	23	84	6	23
				4	1	8	34	100	8	34
				5	0	9	45	6	9	45
	detects row with max value>>>>>>>>>>>>	4							4	4
	detects column with max value>>>>>>>>>>	3							3	3
	this use of formulas only detects the maximum value after searching both tables
	and then finds which table (sheet) contains the maximum value
	and then details the relevant row and column

<colgroup><col><col span="5"><col><col span="5"><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

I thought about it some more and came up with this!