Join comlum letter and row number

[MITTENCAT]

Hi, I have done this before but totally cant remember how to do it ! If I know a column letter say A, and cell contains the row number how do I join them so it can be used in a formula ?

 

[SkipVought]

HI,

=INDIRECT("A"&C5)

Where C5 contains the row number.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MITTENCAT]

Thanks Skip - still a small problem,the data is in a separate sheet so it looks like this =INDIRECT('Sheet1'!"A"&C3) says error in formula

 

[SkipVought]

C3 is on some other sheet, not the string, "A"!

Or is it
=INDIRECT("Sheet1!A"&C3)

???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MITTENCAT]

C3 has the row number in the sheet I want the data to come into, the letter "A" is in sheet1

 

[SkipVought]

????

Your comments are confusing. Where are we?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Try
=INDIRECT("A"&Sheet1!C3)

 

[SkipVought]

The OP has not been clear. We are guessing!!!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MITTENCAT]

Sorry, the workbook has two sheets - Sheet1 and Sheet2 - by using the "Row" formula I can locate the data I need in sheet1 and it gives for example row 300.

So I now know in sheet1 the data is in row 300, so I now need the information from Column A again in sheet1. The contents of that I need in a cell in Sheet2. So I am trying to reference out of one workbook into another.

Hopefully this is better...

 

[SkipVought]

Sorry, you continue to give mixed signals.

"the workbook has two sheets - Sheet1 and Sheet2"
"So I am trying to reference out of one workbook into another"

What happens in vagueness stays in vagueness.

But I'm going to restate what I think you have stated.

You want a formula in Sheet2 that references data in Sheet1, where the desired data is in column A and C3 contains the row number.
[tt]
=INDIRECT("Sheet1!A"&Sheet1!C3)
[/tt]

Although this will yield a correct solution, it is a very bazaar and unconventional approach. In most cases you will have a Table and you will employ some sort of lookup to retrieve data from the Table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MITTENCAT]

Thanks the formula above didn't work either but I've now done it a different way with index and match gives me the results I require, thanks for the help

 

[SkipVought]

Well the formula works with the assumptions that I stated. You again failed to state your case unambiguously!

You don't need INDEX & MATCH, as you have provided nothing to match!

It can, however, be accomplished with INDEX.

Please provide to our members, the formula that you eventually used, as many members browse these forums and gather useful information for their own use. It is both customary and courteous.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MITTENCAT]

I re-rote the formula to use index and match, this is what it looks like: =IFERROR(INDEX('Sheet1!D11423,MATCH(MIN('Sheet2'!R2:R1423),'Sheet1'!R1:R1423,FALSE),1),"")

 

[SkipVought]

Well no wonder!

The references you just posted have absolutely no resemblance to the information you, up to this point in time, have posted. Are we on the same planet?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MITTENCAT]

Not sure, i'm on Jupiter where are you ?

I did say I did it another way as I couldn't get the formula to work as I was trying, this did the job, and why it looks the way it does