Formula help for a linked cell to another worksheet

Hi,

I am a new forum user and have a decent amount of knowledge of Excel (but I am lost in formulas). I hope I am not asking something for which a simple search would have led to the answer, but I wouldn't know what to search for.

I have a workbook with several sheets in it and the data on the primary worksheet is to be transposed as a linked cell in the secondary worksheet. The basic formula is this:
='Primary work sheet'!$A$1

Now, I have linked cells for column A, B, C, D, etc. The rows that I want to reference in the secondary work sheet are a selection from the primary worksheet (not all of them, not sequential either).

Is there a way to have all the formulas in place, then to enter the row number in only one cell and automatically change the same data (row number) in the other cells? Maybe some kind of formula that says:
='Primary work sheet'!$A$x where x is a row number that is entered somewhere on the secondary sheet? So that, by simply entering the value for x, the cells with all the formulas would change (='Primary work sheet'!$B$x, ='Primary work sheet'!$C$x, etc.).

I hope I am making myself clear (but I don't think I am!). Thanks to those who attempt to decipher this!

FL

Hello and welcome to the forum.

Try the INDIRECT function. Something along the lines of =INDIRECT("'Primary work sheet'!$A$"&Z1) where the desired row number is in cell Z1.

That being said, it is difficult for us to provide an optimized solution without seeing a small representative sample of your data along with the desired output.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Hi Stringjazzer and Falcondude, and thanks for your quick replies!

I tried the INDIRECT function first because it seemed simpler, and it worked straight away! That was awesome. I can hardly believe you understood my need. Will definitely come back when I am stumped again!

Much appreciated!
FL

Great, glad we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

FYI, here is another choice that uses a non-volatile function:

=INDEX('Primary work sheet'!A:A,Z1)

If Z1 = 4, then the formula will return the value in A4 of the 'Primary work sheet' sheet.

Hi again,

I have already done all the modifications using the INDIRECT function. I don't know much about volatility (I consulted the link you provided). Will it impact my spreadsheet's functionality? I guess the question is, should I go through the trouble of redoing everything using the INDEX function instead?

Thanks again,
FL

It depends on a few things including how many cells you are using the volatile function in.

If you do not notice a performance decrease, then I would leave it alone.

If it ain't broke, don't fix it.

Excellent, thanks again for the help!
FL

Originally Posted by 63falcondude

It depends on a few things including how many cells you are using the volatile function in.

If you do not notice a performance decrease, then I would leave it alone.

If it ain't broke, don't fix it.

You're welcome. Happy to help.