Concatenate using separate cell contents in formula to reference data in 2nd worksheet

[Pondridge]

I have a file with two worksheets. Worksheet 1 is a list of Names, addresses, etc. Worksheet 2 is a form with nine formula's in it to reference data from the first worksheet. The current formula's; =concatenate(sheet1!B24," ",sheet1!D24) and =sheet1!K24 work, but each time I need to change individuals in the form, I have to go in and change the number in each formula. (The 24 represents the line number from worksheet 1 and is consistent in each formula for an individual). What I would like to do is have one cell (J17) on the form where I can type in a number in which would change the line number in all nine formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=concatenate(index(sheet1!B:B,J17)," ",index(sheet1!D:D,J17))

 

[Pondridge]

=concatenate(index(sheet1!B:B,J17)," ",index(sheet1!D:D,J17))

This works great. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.