How to Freeze specific ranges in Formula all together

[navneetvyas]

Hello All

I want to freeze the formulas (F4) in whole column in Microsoft Excel, Is it possible to freeze together, not to freeze one by one. I freeze it one by one but that's actually a pain when you have hundreds of formulas to freeze.

[FDibbins]

Hi and welcome to the forum

Im not entirely sure I understand your question, but if you are talking about absoluting a cell/range ($), then maybe this will help...

$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across

[navneetvyas]

Hi Ford

Thanks for your reply

However am looking for way which can help to put the absolute in all the formulas for specific range in one go, as if I have hundreds of rows with similar formula then its so irritating to freeze the formula individually in every column or rows

[FDibbins]

Sounds like you are indeed talking about absoluting? Once you get the formula sorted out with the necessary absoluting, you dont need to change it for every row, you just copy it down/across. If it is not working when you do that, then maybe you dont have the absoluting applied properly?

Can you provide a few examples?

[navneetvyas]

Hi Ford

Got your point but just for example I have attached the excel sheet, wherein "Result Data" I want to capture the stock value from "Source Data", in this case I have to drag the formula horizontally and vertically both as I want to freeze the product name for every individual column pertaining to specific shop code

[FDibbins]

Im not entirely sure what you are trying to do, but I *think* this will give you what you want?

=IFERROR(INDEX('Source Data'!$G$3:$X$5,MATCH($C4,'Source Data'!$D$3:$D$5,0),MATCH('Result Data'!F$3,'Source Data'!$G$3:$X$3,0)),"")

[navneetvyas]

Thanks a Lot Ford It Works, you have saved lot of my time

Once again Thanks a ton

[FDibbins]

Happy to help and thanks for the feedback