[code] Your Code [/code] (or use the # button), By kosherboy in forum Excel Formulas & Functions, By MAGICofSeth in forum Excel Programming / VBA / Macros, By Bonzopookie in forum Excel Formulas & Functions, Search Engine Friendly URLs by vBSEO 3.6.0 RC 1, Formula to read cell references as number value, Getting a formula to read multiple worksheet data (same cell range in each sheet), How to not read a cell with a formula as a value, CAN YOU SET GRAPH TO READ, FORMULA IN A CELL AS A BLANK CELL. Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Instead, Excel probably thinks the formula is text. The cell format category will be Text. However, in the data set the lookup value is linked with multiple different dates and I would like to know if there is one excel formula I can use to differentiate my look up value to pick up the date I need without concatenating the data or having . GJG_MS In the article, you will find an Excel workbook like the image given below to practice on your own. You might use this kind of test to only run a calculation if there is a value in A1: Now, Press ENTER. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. Our array for the INDEX formula will be columns A and B. As add-ins outside the file specification they will never be supported. If(AA1=BB1="ok","ok","check") but currently is not recognising the values. Read More: Excel Showing Formula Instead of Result (8 Reasons). December 03, 2020. The cell still displays the formula. is not blank), we can assume the task is complete. In the worksheet shown,column D is is used to record the date atask was completed. We are here to help. The resulting output will be like. I hope this will satisfy your needs. We have two sheets named Dataset8 and Dataset9. You can use IF to test for a blank cell like this: In the first example, we test if A1 is empty with ="". The cell displays the value. The logic now is if cell D5 is blank. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, Excel: Use value of cell instead of formula in another formula, The open-source game engine youve been waiting for: Godot (Ep. The formula has been removed from the formula bar. My formula in another cell is supposed topick up the OK or check. This function will help you to remove space automatically. After that, for a reference of our cell, we will use INDEX and MATCH. In I4, type the following formula: =SUM(INDIRECT(I3) Press Enter to complete the formula. Thanks for the incredible website! There are some functions in Excel that designed to do exactly this. If you then edit the formula in the linked cell, the formula is displayed in the cell rather than the value that is returned by the formula. This could be especially useful if you have a large data set and you want to find out the exact position of the lookup formula result. 542), How Intuit democratizes AI development across teams through reusability, We've added a "Necessary cookies only" option to the cookie consent popup. To reverse this behavior, wrap the NOT function around the ISBLANK function: = NOT ( ISBLANK (A1)) // TRUE if A1 is NOT empty By adding NOT the output from ISBLANK is reversed. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula: =IF (CELL ("type",A1)="v",A1*2,0) In a situation, suppose we have entries of certain Categories and Products in a dataset like the image below. Apply Excel Power Query Tool to Show Value Instead of Formula, [Fixed!] Only value is visible here instead of formula. Now, using this dataset we will show you how to check if a cell contains text and then return the value in Excel. To do this, follow these steps: Select the cell with the formula, and then click Cells on the Format menu. For this case, cell formatting will be prospective. Not the answer you're looking for? The most common reason for this error is the wrong data type used in the cell references. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. To use ISBLANK directly, you can rewrite the formula like this: Notice the TRUE and FALSE results have been swapped. Use of Right Button of Mouse to Display Just Value in Excel, 6. Drift correction for sensor readings using a high-pass filter, Centering layers in OpenLayers v4 after layer loading. The cell still displays the formula. Manually delete the preceding Apostrophes. If sheet 1 has John in B2 and sheet2 has Jacob in B2 and i want an output in sheet3, where if sheet 2 has Jacob then ignore or blank. Have you entered a formula, but Excel is not showing a result? The syntax of the IF function is: It results in one pre-selected value either [value_if_true] or [value_if_false] depending on the logical_test output; true or false respectively. Is it ethical to cite a paper without fully understanding the math/methods, if the math is not relevant to why I am citing it? If (AA1=BB1="ok","ok","check") but currently is not recognising the values. HI, I want to pull text from multiple sheets and ignore some specific text as blank or space. This formula will return TRUE when A1 is not empty and FALSE when A1 is empty. Use code tags for VBA. You can pull text from multiple sheets and ignore blank or space by using the TRIM function. We can see that formula is removed at the formula bar. Has the term "coup" been used for changes in the legal system made by the parliament? When using lookup formulas in Excel (such as VLOOKUP, XLOOKUP, or INDEX/MATCH), the intent is to find the matching value and get that value (or a corresponding value in the same row/column) as the result. How to do it? TechCommunityAPIAdmin. : This excel error occurs for multiple reasons depending upon the formula we use. Use of F2 and F9 Functional Keys to Hide Formula and Show Values 5. The cell format category will be Text. on We get the context menu by pressing the right button of the mouse. Hello, this is Alok. February 08, 2022, by The INDIRECT function returns a valid reference from a text string. If D5 is empty, IF returns an empty string ("") which displays as nothing. Tried to add text to cell AA1 : =TEXT (if (B1=C1,"ok","check"),"TEXT") Still the values are not recognised. January 03, 2023, by Utilize IF & ISTEXT Functions to Check If Cell Contains Text Then Return Value, 3. The reason for editing the formula is that formatting the cell to Generalwill not have any impact on the cell's existing contents. Read More: How to Return Value of Cell Not Formula in Excel (3 Easy Methods). Example . We will use the function keys of the keyboard here. Therefore, if you enter a formula that links one cell to a cell formatted as text, the cell with the formula is also formatted as text. How do I apply a consistent wave pattern along a spiral curve in Geo-Nodes 3.3? Click OK. The problem is that the actual formatting applied by conditional formatting is not available in a cell formula. Find centralized, trusted content and collaborate around the technologies you use most. In our case, we can use the VLOOKUP function to find an exact or approximate match in a column. Select the cell with the formula, press F2, and then press ENTER. Every once in a while, you might find Excel behaving in a bizarre or unexpected way. We will use the keyboard shortcut to avail the Paste Special tool and show values by removing the formula. It takes the row and the column number and gives you the cell address of that specific cell. In many cases, we try to search for a text or string in a range in Excel. Or. Excel has a feature called Show Formulasthat toggles the display of formula results and actual formulas. But if you need this to be more fool-proof, use the one covered in the next section. We will use the Power Query feature of Excel to view values instead of formulas. It is the simplest method. Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge. The values in the other cells are from formulas. When and how was it discovered that Jupiter and Saturn are made out of gas? is there anyway this formula can be revised to return like for example: found word "wow" and return the word into the cell instead of TRUE/FALSE? The single quote reminds you that the formula has been intentionally converted to text: Later, you can then come back later to work on the formula again, starting where you left off. Hope this will solve your problem. But in some cases, instead of getting the value, you may want the formula to return the cell address of the value. As you can see the formula returns the same value or blank cell based on the logic_test. It takes the row and the column number and gives you the cell address of that specific cell. And then simply wrapped it within the CELL function and asked it to return the cell address of this value that I get from the INDEX formula. Read More: How to Return Value If Cells Contain Certain Text from a List. Suspicious referee report, are "suggested citations" from a paper mill? In this scenario, you'd change the formula to =IF (ISTEXT (A2), "Yes", ""). Step-01: Again, Select the output cell F5. Keep pressing the button. Explanation The Excel ADDRESS function returns the address for a cell based on a given row and column number. 542), How Intuit democratizes AI development across teams through reusability, We've added a "Necessary cookies only" option to the cookie consent popup. Does With(NoLock) help with query performance? our column number to be any other number, for example, number 3, we would get an incorrect result: We still get the right number for rows, which is something. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide. Why does Jesus turn to the Father to forgive in Luke 23:34? Below is the syntax of the ADDRESS function: Now, lets take an example and see how this works. With a little troubleshooting, you can get things working again. My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. Use Paste Special Shortcut to Show Values Only 4. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Now we will paste it into the same cell. In other words, if a formula returns an empty string in a cell, Excel interprets the cell as "not empty". Hope you find discussed methods super easy to follow. on Choose the account you want to sign in with. a negative number will round to the left of the decimal. I want to provide solutions to various Excel-based problems. With Show Formulas enabled,columns are expanded, and every formula in a worksheet is displayed with no results anywhere in sight, as shown below. Show Formulas is meant to give you a quick way to see all formulas in a worksheet. Method-1: Using DATEVALUE Function to Return Value If Cell Contains Date. Excel has various built-in functions to match and fetch value if a cell contains text or string. To check if Show Formulas is turned on, visit the Formula tab in the ribbon and check the Show Formulas button: Show Formulas enabled -just click to disable. First, we input ADDRESS, which is a basic formula to return the cell reference. The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. the string that is being search consists of "string 1|string 2|string 3" matched with for example in the D2 column is "string 2" so that means it can not do a match, but more of find string? Cell C11 is copied. October 14, 2022, by I have used the INDEX formula as the second argument to get the department for the employee id KR256. In the worksheet shown, we use the second idea in cell E5 like this: If D5 is "not empty", the result is "Done". Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. (0 members and 1 guests), ________________________________________________________, 1. So in this article, we will discuss 7 methods on how to show value instead of formula in Excel with proper illustrations and explanations. In this article, we explained how to show value instead of formula in Excel. Best practices and the latest news on Microsoft FastTrack, The employee experience platform to help people thrive at work, Expand your Azure partner-to-partner network, Bringing IT Pros together through In-Person & Virtual events. by Sometimes, we have a criterion in a range of cells to match the result in another range of cells. However, if you accidentally trigger this mode, it can be quite disorienting. Launching the CI/CD and R Collectives and community editing features for Get formula from Excel cell with python xlrd. Check If Cell Contains Text with AND Condition Applying Combined Functions, How to Find a Named Range in Excel (4 Easy Ways), How to Apply Cubic Spline Interpolation in Excel (with Easy Steps), How to Add Text Prefix with Custom Format in Excel (4 Examples), [Fixed!] on Below is the syntax of the ADDRESS function: =ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text]) where: row_num: Row number of the cell for which you want the cell address. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. For example, the ADDRESS function with 1 for both row and column like this: = ADDRESS (1,1) returns "$A$1" as text. All formulas in Excel must beginwith an equal sign (=). lilian51129 As the formula is copied down it returns "Done" when a cell in column D is not blank and an empty string ("") if the cell is blank. How to Check If Cell Contains Text Then Return Value in Excel, 9 Easy Ways to Check If Cell Contains Text Then Return Value in Excel, 1. Show Formulas toggles the display of every formula in a worksheet. Asking for help, clarification, or responding to other answers. I am testing this =IF(NOT(NOT(SUM(--NOT(ISERR(SEARCH($D$2:$D$21;$B2)))))), CONCAT("Found word", $YourWord), "Not found"), $YourWord is the cell of your word logical_test will be your formula, hmm its not working on my end : =IF(NOT(NOT(SUM(--NOT(ISERR(SEARCH($D$2:$D$21;$B3)))))); CONCAT("Found word";B3); "Not found"), yes if its just {=NOT(NOT(SUM(--NOT(ISERR(SEARCH($D$2:$D$21;$B2))))))} it worked and inputted as ranged, using ctrl + shift + space, tested the formula, if the column is empty it returned the $D$2 word, and if it's found, its returning the $D$2 word too, instead for example it should have returned $D$4 string. Use Paste Special from Context Menu to Display Values Only 3. set 2 to be a number that matches however many decimal points you are using. In this section, we will discuss how to remove the formula from multiple sheets at a time and show values. RV coach and starter batteries connect negative to chassis; how does energy from either batteries' + terminal know which battery to flow back to? However, if you add a single apostrophe before the equal sign Excel will treat the formula as text and let you enter without complaint. of a specific cell. You can download the workbook to practice yourself. To lookup a value and return corresponding cell address instead of cell value in Excel, you can use the below formulas. A subtle variation of this problem can occur if there is one or more spaces before the equal sign. Therefore, if the column contains a date (i.e. Sharing best practices for building any app with .NET. Normally, if you try to enter a formula in an unfinished state, Excel will throw an error, stopping you from entering the formula. Let's start with the most obvious and simplest way to check if a cell contains a date and returns a value. rev2023.3.1.43269. Our goal is to help you work faster in Excel. ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. You may need to enter cell edit mode (click into the formula bar, or use F2, then enter) to get Excel to recognize the format change. Remove Formula from Multiple Sheets to View Values Using the Paste Special Feature, 7. A new sheet will create, where the formula is removed and the value is visible at Cell C11. Arin Islam. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. Although a broken formula is never fun, you can sometimes use the "formula as text problem" to your advantage, asa way to save work in progress on a tricky formula. Read More: [Fixed]: Excel Formula Not Showing Correct Result (8 Methods). =INDEX(A1:J10,C3,C4) In the example above, the range specified in the INDEX formula is "A1:J10", the row is cell "C3" ("3"), and the column is cell "C4" ("5"). We are not asking Excel if A1 is blank, we are literally counting the characters in A1. Combine IF & COUNTIF Functions to Check If Cell Contains Text, 6. Connect and share knowledge within a single location that is structured and easy to search. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. To test if a cell is not blank (i.e. 10:16 AM Formula 1 To return the cell absolute reference For example, you have a range of data as below screenshot shown, and you want to lookup product AA and return the relative cell absolute reference. A dataset often contains more than one text string. 1. If, however, you happen to need this information, we will explain how to do this in the example below. Get cell value. In Microsoft Excel, if you enter a formula that links one cell to a cell that is formatted with the Text number format, the cell that contains the link is also formatted as text. Now I am trying to use this percent in D4 to multiply a number in cell A1, 176522.5 which should give me the answer 4766.1075 however Excel is returning the value 4758.19902 which is incorrect. How does a fan in a turbofan engine suck air in? I firstly created a cell D4 = AVERAGE (D1:D3) formatted in a percent 2.70%. You can use IF to test for a blank cell like this: In the first example, we test if A1 is empty with ="". To display both "Done" and "Not done", you can adjust theformula like this: Another way to solve this problem is with theISBLANK function. Use Paste Special Shortcut to Show Values Only, 4. First, you may have forgotten the equal sign. Apply ISNUMBER & SEARCH Functions to Find If Cell Contains Text, 4. Find out more about the Microsoft MVP Award Program. Select the format code that you want the cell to display, and then click OK. Excel MATCH Function Not Working, SUMIFS to SUM Values in Date Range in Excel, Formula for Number of Days Between Two Dates, In the beginning, insert the following formula in. LOOKUP() LOOKUP() LOOKUP() LOOKUP() lookup_value lookup_vectorresult_vector lookup_value Press Enter and the value of the letter. Read More: If Cell Contains Specific Text Then Add 1 in Excel (5 Examples). Now, press the right button of the mouse. And the best part about using this formula is that it is not tied to the first cell in the worksheet. Find centralized, trusted content and collaborate around the technologies you use most. It's an invaluable resource for me. When you open an Excel file with openpyxl you have the choice either to read the formulae or the last calculated value. Use IF Function to Check If Cell Contains Text Then Return Value in Excel 2. Its called the CELL function (and it can give you a lot more information about the cell than the ADDRESS function). What happened to Aham and its derivatives in Marathi? Is there a way to only permit open-source mods for my video game to stop plagiarism or at least enforce proper attribution? I love traveling, reading books, playing cricket. January 31, 2023, by This formula uses the Excel IF function, combined with the less than and equal signs (=), to test if the value in cell C8 is less than or equal to the value in cell C5.If the test is TRUE the formula will return a "No" value, alternatively if the test is FALSE the formula will return a "Yes" value. 2. The ISNUMBER function returns true or false depending on a match of SEARCH text in an absolute range. If so, set the formatto General, or another suitable number format. From Method 8, we know how multiple text strings in a cell get matched. Normally, when you enter the INDEX formula in a cell, it returns the value because that is what its expected to do. For the following formula to work, we will presume that we already know the column number of the thing that we are looking for. See #17 in this list for more info. We will apply a quick option from the Ribbon in this section to display values instead of formulas in Excel. We will enter the name of LeBron James in cell E2, and the following formula in cell F2: This formula is fairly easy. For the rows, we will use MATCH to find the value of our cell in the range A1:A7. Suppose we are given a few numbers as below, so in this scenario, the SUM array formula for absolute values would be =SUM (ABS (A2:A6)). There are severalsituations that might cause this behavior. in the case of a percentage, you may need to add 2 to the number of decimal places. Choose, The power query window appears now. Sometimes, when people mention a formula online, they will use quotes, like this: In Excel, quotes are used to signify text, so the formula will not be evaluated, as seen below: Note: you are free to use quotes inside formulas. Cell with python xlrd Maruf Islam, an engineer and Excel & VBA content developer on Exceldemy to show instead. Formula instead of formula in Excel and column number and gives you the cell than address. See all formulas in Excel must beginwith an equal sign instead of result ( Reasons. Other answers try to search then click cells on the logic_test can get things working Again by using the function! Designed to do this, follow these steps: Select the cell address of specific. Explained how to do this, follow these steps: Select the cell function and! Following formula: =SUM ( INDIRECT ( I3 ) Press ENTER and the value of cell. Topick up the ok or check Contains specific text as blank or space find centralized, trusted content collaborate. Special Tool and show values 5 give feedback, and then return value if cell text! Or blank cell based on the Format menu solving problems, finding workable solutions and! That formula is that it is not empty and FALSE results have been swapped cell, try... Many cases, instead of getting the value of our cell, we will a. With ( NoLock ) help with Query performance another suitable number Format Methods ) of mouse display! Below to practice on your own share knowledge within a single location that is structured and easy search! Using this dataset we will use the Power Query Tool to show value instead of getting value! Methods ) not tied to the left of the keyboard Shortcut to show value instead of formula Excel. Match in a while, you may want the formula returns an empty string in a column trusted. Least enforce proper attribution string excel return value of cell, not formula & quot ; ) which displays as.! However, if the column number Excel probably thinks the formula, Press the right of! Use of right button of the decimal like the image given below to practice on your own,.. Rows, we explained how to return the cell than the address for cell... Your answer, you can pull text from a paper mill & COUNTIF functions check! The letter example below use ISBLANK directly, you can get things working Again a dataset often Contains than. Formatto General, or responding to other answers LOOKUP a value and return corresponding cell address of the Shortcut... Value or blank cell based on the logic_test are not asking Excel if A1 is.. Below is the syntax of the part I really like to take on challenges an engineer and Excel & content!, lets take an example and see how this works as `` not empty and FALSE results have been.. Return corresponding cell address instead of formula results and actual formulas, or responding to answers!, lets take an example and see how this works been swapped Ribbon... In a while, you can pull text from multiple sheets at a time and show values only.. And how was it discovered that Jupiter and Saturn are made out gas... Takes the row and column number v4 after layer loading the number of decimal places other questions tagged, developers... Reasons ) clarification, or another suitable number Format, Where developers & worldwide! Out more about the cell with the formula from multiple sheets to view values instead of (! Indirect function returns a valid reference from a paper mill Services LLC Associates Program, engineer. True result, and then return value of our cell in the other cells are formulas. Been removed from the formula bar you a quick option from the,. Use this kind of test to only run a calculation if there is one or more spaces before the sign... Only run a calculation if there is one or more spaces before the equal sign, Press the right of. Lookup ( ) LOOKUP ( ) LOOKUP ( ) LOOKUP ( ) LOOKUP ( ) lookup_vectorresult_vector... Formula not Showing a result read more: [ Fixed! or another suitable number Format formula like this Notice... Connect and share knowledge within a single location that is structured and easy to follow one. Text as blank or space excel return value of cell, not formula the cell address instead of getting the value cell... Only permit open-source mods for my video game to stop plagiarism or at least enforce attribution. Isnumber & search functions to match and fetch value if cells Contain Certain text from multiple sheets to view instead. Show formulas is meant to give you a lot more information about the cell with python xlrd column! To complete the formula has been removed from the formula, but Excel is not recognising the in!, playing cricket section to display Just value in excel return value of cell, not formula has various built-in functions to if... Interprets the cell with python xlrd: Notice the TRUE and FALSE when A1 is not blank (.! Do this in the worksheet turn to the Father to forgive excel return value of cell, not formula Luke 23:34 share knowledge within single... If & COUNTIF functions to check if cell Contains specific text then return,! I want to sign in with or check now is if cell D5 is empty working Again function. Method-1: using DATEVALUE function to check if cell Contains specific text then Add 1 in Excel legal. Trigger this mode, it returns the address function: now, this. Negative number will round to the Father to forgive in Luke 23:34 cases we. The equal sign participant in the Amazon Services LLC Associates Program, an engineer and Excel & VBA developer. Are literally counting the characters excel return value of cell, not formula A1: A7 function runs a logical test returns... Firstly created a cell based on a given row and the column number and you! And charts if, however, if the column number and gives you the cell references D5 is blank privacy... Wrong data type used in the article, you can see that formula removed! But currently is not Showing a result other cells are from formulas ( i.e how... Complete the formula like this: Notice the TRUE and FALSE when A1 is empty, if a cell matched! Will create, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists private! The column number and gives you the cell function ( and it can give a. Is is used to record the date atask was completed a value in 2! Report, are `` suggested citations '' from a List 5 examples ) tables, formatting! Helps you quickly narrow down your search results by suggesting possible matches as you type the Special! Excel if A1 is empty, if the column Contains a date ( i.e want provide! Reasons ) use INDEX and match OpenLayers v4 after layer loading easy to search for a text string with... This function will help you ask and answer questions, give feedback, and run. Get things working Again 8, we will explain how to show values by removing the formula has been from. And it can give you a quick way to only run a calculation if there is one or spaces... Layers in OpenLayers v4 after layer loading has been removed from the Ribbon in this section, we try search... Its called the cell address of that specific cell problem can occur if there is one or more before! Associates Program, an engineer and Excel & VBA content developer on.! Sheets and ignore some specific text then return the cell with the is... Hi - I 'm Dave Bruns, and then click cells on the logic_test actual! Find centralized, trusted content and collaborate around the technologies you use most does with ( NoLock ) with... Been swapped january 03, 2023, by Utilize if & COUNTIF to! In other words, if the column Contains a date ( i.e: how to do this..., are `` suggested citations '' from a paper mill a reference of our cell Excel. Beginwith an equal sign you the cell with the formula like this: the. That it is not empty '' Method 8, we will Paste into... To remove the formula if there is one or more spaces before equal... Removed from the formula like this: Notice the TRUE and FALSE results have swapped... Show value instead of result ( 8 Reasons ) ) help with Query performance information, we have criterion! That designed to do this in the legal system made by the INDIRECT returns. Paste it into the same value or blank cell based on the logic_test text from text. Ci/Cd and R Collectives and community editing features for get formula from Excel cell the! Coworkers, Reach developers & technologists worldwide text from multiple sheets at a time and values! Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, developers. Clicking Post excel return value of cell, not formula answer, you may need to Add 2 to the first cell in the cell address that! Address instead of getting the value and 1 guests ), ________________________________________________________, 1 rewrite the is... Formula, and charts and return corresponding cell address instead of formulas a! More spaces before the equal sign use INDEX and match functions in Excel along... Curve in Geo-Nodes 3.3 to pull text from multiple sheets at a time and show values 5 function of! And show values the ISNUMBER function returns TRUE or FALSE depending on a row. If function runs a logical test and returns one value for a reference our... That the actual formatting applied by conditional formatting, and then return the cell address of that specific.... A lot more information about the cell excel return value of cell, not formula following formula: =SUM INDIRECT...