CODEWITHSUNDEEP
excelexcel-formula
inky
inky

Reputation: 137

Referring to a cell value in Name Manager of Excel

In the name manager, I want a range formula to refer to a cell which I can change the value of.

I have the following formula as a defined name in Name Manager

=OFFSET(Sheet2!$KO$3:$NH$3,0,1,1,NumRows)

I want the 3 in the above to refer to cell "A1", the intention is that I can enter a number into cell "A1" for example 12 so that the defined name will then change to 

=OFFSET(Sheet2!$KO$12:$NH$12,0,1,1,NumRows)

I have a graph that is linked to the formula above so that when I change the cell value in cell "A1" my graph will then find the corresponding row in my data matrix and update accordingly.

Can someone assist, please?

Upvotes: 2

Views: 416

Answers (2)

user4039065
user4039065

Reputation:

try,

=OFFSET(Sheet2!$KO$1:$NH$1, Sheet2!$A$1-1, 1, 1, NumRows)

... or discard OFFSET altogether.

=INDEX(Sheet2!$KO:$KO, Sheet2!$A$1):INDEX(Sheet2!$KO:$NH, Sheet2!$A$1, NumRows)

TBH, I find your use of NumRows confusing since it refers to the number of columns in your original OFFSET.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166316

=OFFSET(INDIRECT("Sheet2!$KO$3:$NH$" & A1),0,1,1,NumRows)

Upvotes: 1

Related Questions