Formula to return the Row of a cell that is referenced by a cell that you must go through.

[ZMBER]

How do I write a formula to return the row# of a cell that is referenced by another cell and I must go through that other cell to learn what cell it is referencing.

In other words:

IF:
"Other"!B3=4
then
A1="other"!B3=4

I want to use cell C1 to Find Row("other"!B3) or to show "3", but C1 needs to adapt to changes in A1.

Therefore you rely on A1 to know that you need to return Row("other"!B3) in C1.

This is because in the future A1 could equal "other"!B4 and in that case C1 needs to return Row("other"!B4), or "4", and not Row("other"!B3) or "3".

thank you for your help

 

[Trebor76]

Hi ZMBER,

Welcome to the MrExcel forum!!

Assuming the row number is housed in cell A1, try this:

=INDIRECT("Other!B" & A1)

HTH

Robert

 

[ZMBER]

I am getting a #REF! error. Do you know why this might be?

 

[ZMBER]

Hi ZMBER,

Welcome to the MrExcel forum!!

Assuming the row number is housed in cell A1, try this:

=INDIRECT("Other!B" & A1)

HTH

Robert

Sorry should have made this a little more clear, A1 does hold the row number, but rather houses the cell that I wish to find the row number of.

 

[ZMBER]

I created a solution using some helper column:

I first needed two module:

Function MyEval(s)
MyEval = Evaluate(s)
End Function

Function CellFormula(c)
CellFormula = c.Formula
End Function
Solution:

Source: Mr. Excel forums

Then I used

=cellformula()

to create a helper column in C1 that would give me the underlying Equation of A1 and then dragged this down

Then In D1 or another column I used

=myeval("=Row("&"=Right(C1,13)"&")")

thank you guys for all your help. I am sure I am not the only one that needs so I hope someone else finds this helpful