Auto changing on the value with the rows and columns.

Question

Hi experts, may need some help. I am trying to build a formula that for the yellow highlight numbers to refer to the first row and when I drag it, it will follow the numbers set in the first row. For the Blue highlighted value, refer to the column on the right; when I drag it, it will refer to the  next column.

Currently, I manually change the value and it will be easier if able to have some expert advice anyway to automise it.

Thanks alot.

Paul Newcome · Accepted Answer

You can use cell references such as

$Name@row

and

[W01]$1

Notice the "$". It locks in that particular piece of the cell reference. The first is locking in the [Name] column reference which will allow you to dragfill left to right but makes vertical dynamic. The second locks in row 1 so that dragfilling down will always still reference row 1, but since the column reference is not locked in, it will update dynamically as you dragfill left to right.