Convert formula to text

[coden23]

Hi, I have the following formula which i want to return as text for me to find it.

Sheet 1
No. Name
1 Gani Abecia
2 Nestor Santos

Sheet 2

No. Name
1 INDEX(Data!$C$9:$C$78,MATCH(B4,Data!$B$9:$B$78,0),1)

the formula will return it is gani abecia
the problem is when searching for name by pressing conrol f i can't search or find gani abecia since it is a formula. how do i do this?

thanks

 

[sheetspread]

paste as text (copy - paste special - values)

 

[coden23]

well that would be easy... but its part of a formula wherein everything is automatic. its a timekeeping sheet i'm developing for a friend where he can download a biometric scanner report and everything will just be filled up with a formula. the control f is for his use to search the sheet if any queries came up

 

[sheetspread]

Do you have to use find/replace? Vlookup or match will find formula results but only via the sheet unless you use VBA. Sorting or filtering will help too.

 

[coden23]

yes sir. since i'm not the user here.. just developing this for a friend. the sheet two will generate a pivot table in sheet 3. if there's a discrepancy in the table, the user will go back to sheet 2 and find the name of the person who has descrepancy in the pivot.

 

[sheetspread]

Then you'll need a VBA input box that pops up for the user to type text. Excel's find function searches cell contents only, not what's displayed.

 

[jasonb75]

Then you'll need a VBA input box that pops up for the user to type text. Excel's find function searches cell contents only, not what's displayed.

That is incorrect.

Press Ctrl f then Alt t and change the "Look In" dropdown from Formulas to Values.

 

[sheetspread]

Yikes, you're right. Sorry for the bad advice.....

 

[coden23]

that helps... but there's no formula for it? i was thinking =text(INDEX(Data!$C$9:$C$79,MATCH(B4,Data!$B$9:$B$79,0),1)) but this wouldn't work

 

[jasonb75]

Your original formula is fine, you said it returns the correct result and any other formula that refers to it will see the result, not the formula.