I am attempting to make a worksheet tool in Excel 2010 that will allow users to pull data from other (new) worksheets that are copied or moved to the workbook. I'm giving the OFFSET formula a try but receive an error with:
=OFFSET(("'"&'Profit Scenarios'!$I$1&"'!"&"$A"&$I$3),16,5)
Profit Scenarios is the name of the worksheet with the tool and in cell I1 users can type the name of the new worksheet (with their data) that they moved to the workbook and wish to analyze. In cell I3 in Profit Scenarios (the tool worksheet), users type the row of their imported data worksheet that has their data labels (users are importing from different databases and the row with the data labels can change; I need to establish the correct reference for the OFFSET formula).
Currently, I have v1 in cell I1 and 1 in cell I3. I want the formula above to evaluate to:
=OFFSET(('v1'!$A1),16,5)
and when I manually enter this formula the function correctly evaluates.
I tried using the ADDRESS function as well. The ADDRESS function evaluates just fine on its own, but when I nest it inside OFFSET as the first variable, I still get the error.
What am I doing wrong?
Does this have anything to do with the fact that OFFSET is a volatile function or something wrong with my quotes?
Please try:
=OFFSET(INDIRECT(("'"&'Profit Scenarios'!$I$1&"'!"&"$A"&$I$3)),16,5)
Form here:
The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells
As was you were feeding it a string.