Using Cell Address in formula

[Kpbeard]

I am using this formula, =@CELL("address",INDEX(A$14:A$3000,MATCH(B10,A$14:A$3000,1))), to find the cell address for B10, which is a date on sheet 1 of my workbook. This formula results in $A$2364 on sheet1. On sheet2 I would like to use the result of the CELL formula, $A$2364 in a formula that is using SUMIFS back on Sheet1!$A:$A as part of the complete formula. I would like to replace the Sheet!$A:$A with Sheet!$A$2364 but have not found a way to do this yet. If I can find a way to do this, then the rest of the formula will work out as I have several variables using the same method described above. Any help would be great appreciated and I'm working on a general spreadsheet example to help explain my problem.

 

[James006]

Hi,

You can use the reference which produces your result ... say A2364 is located in cell A1:

=INDIRECT("Sheet2!"&A1)

 

[Kpbeard]

James, Thank you for your response. When I put in the indirect formula you provided it returns a blank cell.

 

[James006]

Would you mind posting your revisited formula ? Have you tried to evaluate it ?

 

[Kpbeard]

These are the formulas on Sheet1, where the 5 digit numbers are dates.

This is the formula I'm trying to figure out.

 

[James006]

A couple of remarks :
1. Do you already have a SUMIF function producing the expected result without the Indirect() element ?
2. In post # 2, have you noticed how Indirect is built ... =INDIRECT("Sheet2!"&A1)

 

[Kpbeard]

In trying to get the value ($A$14) in cell A10 on Sheet1,
based on the formula =@CELL("address",INDEX(A$14:A$3000,MATCH(B10,A$14:A$3000,1))).

On Sheet2 in cell A5 I have the formula =indirect("Sheet1!"&A10), this returns #REF!

When I evaluate the formula, there is nothing there that shows an error.

Thank you for your questions and ideas.

 

[MARK858]

=INDIRECT("Sheet1!A10")

Book1.xlsb
	A
1
2
3
4
5
6
7
8
9
10	$A$14
11
12
13
14
15
16
17
18
19
Sheet1

Book1.xlsb
	A
1
2
3
4
5	$A$14
6	#REF!
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
A5	A5	=INDIRECT("Sheet1!A10")
A6	A6	=INDIRECT("Sheet1!"&A10)

 

[Kpbeard]

Success!!! The formula format in A5 of your response is what I've been looking for.

Thank you a thousand times!

 

[MARK858]

You're welcome

Another option

Book1.xlsb
	A
4
5	$A$14
6
Sheet2

Cell Formulas
Range	Formula
A5	A5	=INDIRECT("Sheet1!"&CELL("address",$A10))