How edit links-brake links

[Meko1]

Hello

I have following formula in cell B6 with indirect function- "=INDIRECT("'[Current.xlsx]2020-2021'!$b$"&SHEET($A$1)-7,TRUE)", which is working perfectly what I want to do, but now I encounter the following problem;- usually when typing formula from other workbook you can just go edit links=> and brake links and gives you the value in the cell instead of formula.
Can you suggest solution.

P.S if the problem is not clear let me know and I will try to explain it better.

Thanks in advance.

 

[footoo]

Breaking links when using INDIRECT function

Hello, I have several cells that get their value from another (open) workbook using the INDIRECT function. But if I try to break links the option is ghosted out - please could you tell me if there is there any way to break the links (other than copy and pasting values)? Thanks, Poiu

www.mrexcel.com

 

[Meko1]

Hello
I read it but it gives no solution to the other guys question, and what I understood it is impossible to break it, am I right? or is there any other way to make the formula just in value format?

 

[footoo]

It says the only way is to copy/pastevalue.

 

[Meko1]

Well sure but, since I need to do it for few hundreds of sheets I would like to make easier and my indirect function works like this, it pastes the value in B6 cell on lets say sheet1, and after adding the next sheet, sheet2 and pasting same formula inside B6 it gives me not the same value, but next value, so reads data from other workbook 1 cell below and pastes it. So after copy-pasting all the data in all those sheets I was thinking to create some kind of macro which would allow it to make all the formulas to go away and just to leave formulas and as I am guessing now it is impossible.

 

[footoo]

To convert B6 on every sheet to value :

Dim ws As Worksheet
For Each ws In Worksheets
    ws.[B6].Value = ws.[B6]
Next