Extracting the row from a cell reference

[Rasmas]

HI

how do I exract only the row from a cell reference

EG
=+'OP JHB'!AI47

What would a code be for extracting only the 47 to a cell

Thanks

[eliW]

On 2002-05-08 00:44, Rasmas wrote:
HI

how do I exract only the row from a cell reference

EG
=+'OP JHB'!AI47

What would a code be for extracting only the 47 to a cell

Thanks

if I understand your question, try

=ROW('OP JHB'!AI47)

Eli

[Colo]

Hi Rasmas,
If you want to get it from only a cell reference, please try this.

Microsoft Excel - fx.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
E1		=	=ROW(INDIRECT(D1))
*	A	B	C	D	E
1	*	*	='OP JHB'!AI47	=MID(formula,FIND("!",formula)+1,LEN(formula))	=ROW(INDIRECT(D1))
2	*	*	*	D2 Cell named as "formula".	*
3	*	*	*	formula of reference range of D2 "=get.cell(6,C1)"	*
Sheet1

[Rasmas]

HI

Thanks that does work But
What I have is a spreadsheet that is a form and pull data from anther working sheet. But we constantly inserting lines in the working sheet. I need to extract the row from a cell in the from that refers to anther cell

eg
In a1 I have the Cell reference =+ImportWorksheet!R1017

Now this reference changes every time

I need to extract 1017 into cell Z1 but cell A1 changes and I dont what to keep typing =row(...)

Thanks

[Rasmas]

HI colo

Thanks for the help, could you please check the "=get.cell(6,C1)" when i try to paste it in the cell d2 it tells me unknow function.

What am I doing wrong

Thanks
Rasmas

[Vas]

If you want to have a reference to a specific cell, you just have to type

=ROW(ImportWorksheet!R1017)

Don't you?

Or possibly
=ROW(INDIRECT(A1))
_________________
/Niklas Jansson

[ This Message was edited by: Vas on 2002-05-08 02:44 ]

[Colo]

Hi Rasmas,
I've sent a sample file to you just now.

[Chris Davison]

On 2002-05-08 01:44, Colo wrote:
Hi Rasmas,
If you want to get it from only a cell reference, please try this.

Excellent table design, Colo

[Colo]

Thanks Chris,
I've just made the code.
Does anyone want it?

[sen_edp]

Hello Colo

Nice design
pass me the code please .
My email is in your private folder

Thanks