Convert formula to text

coden23

New Member
Joined
Nov 8, 2011
Messages
10
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

coden23

New Member
Joined
Nov 8, 2011
Messages
10
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
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

New Member
Joined
Nov 8, 2011
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,937
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

coden23

New Member
Joined
Nov 8, 2011
Messages
10
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,937
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top