Using INDIRECT to create Reference

Greetings Friends!

Help with what should be a simple task that has me stumped please.

I'd like to place a row number in a separate cell to use in a formula.

Any help here?
Thanks!

Try

=INDIRECT("Sheet2!A"&C1)

Thanks for the help, for sure.

Glad we could help. Thanks for the rep!

If you don't mind too much . . . in an attempt to get a grasp on these concats, what if I needed to edit the following on my Sheet2:

=INDIRECT("'[New Spreadsheet.xlsx]2016'!$R$"&$D$2)

Since I have tabs (Sheets) named: (2015, 2016, 2017) and column Sheet2!A1 contains 1Q16, I'd like to modify this to select the proper tab by replacing the 2016 (highlighted in blue) and make it "20"&RIGHT(A1,2), so that as A1 changes, so will the source.

Is it possible to do this in the middle of a text string?

If the workbook is closed. You can't use INDIRECT to pull info from it.

There are some UDF that does work on closed workbook... however, there are some limitations.

One such example:
http://www.ashishmathur.com/tag/morefunc/

It's difficult (at least for me) to try to build INDIRECT formulas without having the workbook in front of me.

That being said, try this:

=INDIRECT("'[New Spreadsheet.xlsx]20"&RIGHT(A1,2)&"'!$R$"&$D$2)

Sorry if this is a duplicate reply. I don't see my first reply to you.
I wasn't actually trying to pull from a Closed workbook. Rather, I only added the workbook name to the formula just to learn if the solution to my formula question would work if using multiple workbooks.
Thanks for the info about not using INDIRECT with closed workbooks.

63falcondude, Thanks for your reply. Sorry if this is a duplicate. I replied to you once but it doesn't show at the site for me anyhow.

Your solution worked and I appreciate your time and the lesson. Unfortunately, I'm still confused because I cannot figure out the logic of the solution. I'm confused with the location of the quotes. Specifically, I cannot understand why the single quotes are placed where they are.
I do understand the outer most double quotes though.

Please explain if you find the time. Thanks again.

As with any formula, you have to surround text strings with "double quotes". That is, you have to put quotes around anything that isn't a cell reference or function.

For example, if you have the word "Red" in A1 and you want your formula to show as "My favorite color is Red", the formula would be ="My favorite color is "&A1

Now using the same logic with the INDIRECT formula, you want the end result to look like this (assuming A1=1Q16 and D2=4): ='[New Spreadsheet.xlsx]2016'!$R$4

You want to put whatever will remain constant in quotes and whatever can change as a cell reference.
=INDIRECT("'[New Spreadsheet.xlsx]20"&RIGHT(A1,2)&"'!$R$"&$D$2)

I understand the basics of the double quotes, but not specifically why in this example:

=INDIRECT( " ' [New Spreadsheet.xlsx]20 " &RIGHT(A1,2)& " ' !$R$ " &$D$2)

What I am seeing, as we go from the outer edge to the center, alternating from double-to-single-to-double quotes, is the &RIGHT(A1,2)& being in double quotes. Does that mean the double quotes are around it because the single quotes would make it just text instead of a reference?

No, the formula should be read like this:

=INDIRECT("'[New Spreadsheet.xlsx]20"&RIGHT(A1,2)&"'!$R$"&$D$2)

where the first set of quotes is for the text string '[New Spreadsheet.xlsx]20 and the second set of quotes is for the text string '!$R$

Okay. I guess my problem is that I've been using parenthesis logic incorrectly with the quotes.
Thanks again!

You're welcome. Happy to help!