Have formula data appear as text, maybe VBA solution?

[Jyggalag]

Hi all!

I currently have this setup, please note that I have hidden all the nonrelevant columns however:

The formula I use is this (thank you @RoryA !)

=IFERROR(INDEX(INDIRECT("'"&H504&"'!$U$3:$U$73"),MATCH(1, (INDIRECT("'"&$H504&"'!$A$3:$A$73")=$C504)*(INDIRECT("'"&$H504&"'!$C$3:$C$73")=$H504),0)),"RANDOM TEXT HERE.")

My issue is that while this is naturally very nice in pulling in data from my other sheet, once you click the actual cell in the current column, it shows the formula rather than the text.

I would like the text to be shown also, and was wondering if I could simply hide column E (or slim it down a lot so it is almost hidden) and then make a new column that automatically replicates all of the text from column E, but where once you click on it, you will be shown the text rather than a formula.

I imagine that this might be possible through VBA coding by altering the entire Column so it just copies the raw data from column E.

Is this possible? I would greatly appreciate some assistance here!

P.S. also open to alternative solutions!

Kind regards,
Jyggalag

 

[6StringJazzer]

once you click the actual cell in the current column, it shows the formula rather than the text.

That is probably some setting you have. Hard to diagnose when that is not what you showed. Do you have the Show Formulas option turned on by mistake?

 

[Jyggalag]

That is probably some setting you have. Hard to diagnose when that is not what you showed. Do you have the Show Formulas option turned on by mistake?

Hi,

I tried this and I do not have this turned on.

My issue is not that the formula shows itself in the cell, because it does not. Rather, I want to have the value/result shown in the formula bar. I figured that this is probably impossible without removing the formula.

My suggestion is thus:

Create a column that copies all values from its adjacent column through VBA, so I can hide the formula column and unhide the values column.

Is this possible? Would highly appreciate if someone knew how to do this!

 

[bebo021999]

To hide formula, try to protect sheet.
1) select all cells, format cell, check unlock
2) select cells with formula, check "lock" and "hidden"
3) protect sheet
Now formula is hidden

 

[Jyggalag]

To hide formula, try to protect sheet.
1) select all cells, format cell, check unlock
2) select cells with formula, check "lock" and "hidden"
3) protect sheet
Now formula is hidden

Hi Bebo,

This makes it a bit to hard the sheet though, no? since it will be password protected

 

[bebo021999]

This code will lock and hide formula, protect sheet with no password

Option Explicit
Sub HideFormula()
ActiveSheet.Cells.Locked = False
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    .Locked = True ' remove this line if you dont want to lock cell (can be deleted)
    .FormulaHidden = True
End With
ActiveSheet.Protect Password:="", UserInterFaceOnly:=True
End Sub

 

[jalrs]

This code will lock and hide formula, protect sheet with no password

Option Explicit
Sub HideFormula()
ActiveSheet.Cells.Locked = False
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    .Locked = True ' remove this line if you dont want to lock cell (can be deleted)
    .FormulaHidden = True
End With
ActiveSheet.Protect Password:="", UserInterFaceOnly:=True
End Sub

Hi Bebo, interesting code here, if i wanted it to be password protected what would i do? Just add the password between the ""?

Thanks

 

[bebo021999]

Hi Bebo, interesting code here, if i wanted it to be password protected what would i do? Just add the password between the ""?

Thanks

Yes. Try it.

 

[Jyggalag]

Hi All,

My initial question may have been a bit ambigious or confusing. I made a new thread, but unfortunately it was too similar.

However, please view: Make VBA code that posts a cells formula as a value

I would still very much appreciate a VBA solution if anybody can provide this

 

[Fluff]

You are asking exactly the same question, so it's not "too similar"