Need help with the & function in Excel. 1

[RyanScharfy]

I have two sheets, Sheet1 and Sheet2

I'm in cell A1
In cell A2 I have a number, for example "22"

In cell A1 I'd like to reference a cell on Sheet2 in the 22nd row, column A.

Obviously if this was a text exercise, I would type in A1 "=Sheet2!D22"

However, I have a few thousand of these to type all throughout column A. I'd rather reference "=Sheet2! "&A2"", but that doesn't really work.

Can it be done?

 

[Wray69]

If I understand you correctly you could use the MATCH function. It would look something like this;

=MATCH(A2,Sheet2!A1:A35,1)

Cheers,

Wray

 

[RyanScharfy]

No, that's not it, but I appreciate the response. Match assumes I already know the client name. I don't. Here's a better description.

On sheet 2, a new client is listed every 10 rows in column A. On sheet1, I have to pull the client names... however, I'm restricted in that the cell to pull the next client is every 12 rows. And I have a couple thousand of these A1 client cells I need to complete.

Basically I work for chimps who come up with spreadsheets which are completely inflexible, yet clever in a Rube Goldberg way.

 

[AnotherHiggins]

Have a look at the INDIRECT function - that's what you need.

=INDIRECT("Sheet2!A"&A2)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[GlennUK]

Or use the INDEX function ...

=INDEX(Sheet2!A:A,A2)



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RyanScharfy]

Thanks all, the Index function did the trick. Woo hoooooo!!!!