keeping a constant number for a column in excel and change row number in different sheet-CodePudding

I have two-sheet in an excel file.

Metric and Overview. I refer to Metric data in overview sheet-like =Metric!BB12.

For me, BB will change based on each file report I create. So when the value is BB next month, I want to move it to BC, BD, BE BF etc., the 12 and other matters in other places are constant.

My ask is ... Is it possible to make BB a variable, and when I move the

CodePudding user response：

You need to refer row number as absolute reference. Use $ dollar sign in-front of row number. Try-

=Metric!BB$12

CodePudding user response：

Use INDEX in combination of MATCH.

Assumptions of data:

Sheet Metric keeps header values in row 1 Headers are text

Example with assumptions: =INDEX('Metric'!$12:$12,,MATCH("ColumnHeaderName",'Metric'!$1:$1,0))

Change "ColumnHeaderName" into the value you want to match against.

Edit: added $ to lock rows if you may want to copy down the formula.