Replacing formula with $ variable cell reference

Hello,

I would like to kindly ask you for help. I need to replace multiple formulas with $ variable cell references by another formulas with same $ variables cell references.
If someone will be able to push me forward on how to, I will be then able to write VBA script to do it for all worksheets.

I have workbook with multiple worksheets where in some rows are formulas, which I need to replace by another formulas:

Sheet01
A1 Value01
A2 -
A3 Value02
A4 Value03
A5 -
A6 -
A7 Value04
...
B1 =VLOOKUP($A1,'Sheet 02'!$1:$1048576,2,FALSE)
B2 some text
B3 =VLOOKUP($A3,'Sheet 02'!$1:$1048576,2,FALSE)
B4 =VLOOKUP($A4,'Sheet 02'!$1:$1048576,2,FALSE)
B5 some text
B6 some text
B7 =VLOOKUP($A7,'Sheet 02'!$1:$1048576,2,FALSE)
...

This works without any problem.

Now I need to replace those formulas in column B to:

B1 =IF(LOOKUP($A1,Sheet03!A1:A20000)=$A1,(VLOOKUP($A1,Sheet03$1:$1048576,2,FALSE)),(VLOOKUP($A1,'Sheet 02'!$1:$1048576,2,"FALSE")))
B2 some text
B3 =IF(LOOKUP($A3,Sheet03!A1:A20000)=$A3,(VLOOKUP($A3,Sheet03$1:$1048576,2,FALSE)),(VLOOKUP($A3,'Sheet 02'!$1:$1048576,2,"FALSE")))
B4 =IF(LOOKUP($A4,Sheet03!A1:A20000)=$A4,(VLOOKUP($A4,Sheet03$1:$1048576,2,FALSE)),(VLOOKUP($A4,'Sheet 02'!$1:$1048576,2,"FALSE")))
B5 some text
B6 some text
B7 =IF(LOOKUP($A7,Sheet03!A1:A20000)=$A7,(VLOOKUP($A7,Sheet03$1:$1048576,2,FALSE)),(VLOOKUP($A7,'Sheet 02'!$1:$1048576,2,"FALSE")))
...

So in general: find any formula in column B and replace it by another formula, while keeping $ variable cell reference for respective row.
Note: there is one $ variable cell reference in current formula. Same $ variable cell reference occurs 4x in new formula!

Would you be able to help, please?
To replace about 3.000 formulas manually would kill me.

Thanks in advance for any helpful tip.
PQK

So basically all the formulas use only the Column A?

So can you not use Ctrl+H and Find "A" and Replace with "$A"?

Or is it that you want to replace the whole formula?

You can enter the converted formula manually once in Cell B1 and drag it down.

If you have Text in between and hence do not want to do this, then

Select the data from Column B > F5 > Alt+S > F > Enter (This will selects only those cells with Formulas) > enter the converted formula manually once in Cell B1 and commit using Ctrl+Enter instead of Just Enter

Let me know if that works for you

Alright!

Is there any commonality which can help you filter (Using Autofilter) only those cells with formulas?

Which version of excel are you using?

Can you attach your sample workbook?

Here!

Put this in cell C8 and drag down

=IFERROR(IF(A8<>"",VLOOKUP(A8,Translations!$A$24:$B$29,2,0),""),"")

I am sorry, I got confused between 2 threads..

Try this -

Select A7:B25 > Ctrl+Shift+L for Auto Filter > From A Column Uncheck Cislo & Blanks from the Filter > Copy Cell B31 & On Cell B8 Hit Enter to paste > Select All Entries except headers from B Column > B8 Being the activecell, click in the Formula Bar & Commit using Ctrl+Enter.

Then you can remove the autofilter.

Glad it helped!