I have a column containing dollar values that I need to be able to search from an input cell
I need to be able to search from an input cell
Finding is one part of the task, but what should be the result?
- Do you want the content of the cell containing the value you searched for to show in another cell?
- Do you want to show the reference (address) of the cell containing the value you searched for?
- Do you want to highlight the cell containing the value you searched for?
Thank you for the reply.
I think it would be most helpful for the row containing the cell that matches the search input to highlight, alternatively; highlighting the matching cell would be nice.
so my ideal situation would be to have a search cell that would take my input ‘amount’ and in someway highlight the matching cell or row in the data table making it easy to see if there is a match within the table/list.
Thank you & Happy New Year!
Hello
see the following sample file for a solution to your question (+ additional comment; thanks for the clarification)
The solution is based on Conditional Formatting using
Condition 1:
Formula is $B4=$B$1 (where $B$1 contains the amount to search for)
Apply Style: BGGreen (custom cell style)
Range: A4:G40
Remark(s)
- Of course you need to adapt the details of your Condition 1 to the requirements of your sheet. This solution is just to demonstrate how you could achieve your goal
- Do not enter text into input cell
$B$1, which is formatted as currency. Just enter the numeric value you want to search for (e.g. enter91.06into$B$1, if you want to highlight row12, which shows(!)$91.06
Hope that helps
Thank you - your example does exactly what I am looking for. However, forgive me for being a novice and new to Open Office, but I have very limited experience with conditional formatting (and Open Office in general) I may need more of a step by step guidance here…I highlight my data table, open CONDITIONAL FORMATTING, in Condition 1 I change to FORMULA IS and enter ‘my’ formula: $mycolumn+first cell=$my column$my cell (for my search entry) Then your “Apply Style: BGGreen (custom cell style)” ;is to then select NEW STYLE and select BACKGROUND COLOR? and name it?..I don’t see an “Apply Style” or a “Range:”
Or is that all to be entered in the formula?
I apologize but I have tried several variations and I clearly have next to no clue as to what I am doing here…Thank you & Happy New Year!
Kevin
Sorry - but you cannot expect a step-by-step procedure without clearly defining your sheet characteristics and that’s why I presented the sample sheet.
Thank you - I understand.
Maybe I am just looking in the wrong place in the drop down…I’m not seeing your “Apply Style:” or “Range:” fields(?)…
I choose ‘Conditional Formatting’ from the tool bar drop down and change “equal to” under the “Condition 1” check box to “Formula is” and then enter “$B4=$B$1” (changing B4 to my P5, and still using “$B$1” for my search box input cell, but then I guess I’m not understanding your “Apply Style:” and “Range:” There’s a “cell style” drop down and a “New Style” button…that’s where I’m lost I think…I hope that makes sense…
There’s a “cell style” drop down and a “New Style” button…that’s where I’m lost I think
OK - let’s start with a preface: Condtional Formatting is based on three things:
- One (or more) Condition(s)
- A Cell Style to be applied, if the Condition(s) is/are met
- A Range of cells, which should be checked for Condition(s) (1) being met and on which Cell Style (2) should be applied (if Condition(s) (1) are true).
Now you seem to be stuck on 2) defining a proper Cell Style for highlighting the cell and you are on the right path, if you see the “New Style” in Dropdown right to Apply Style text. To define you desired cell style (which in other words means: The format of your cells matching condiotion(s), or how you want 'em to look like), select New Style and make your settings in
- Tab: Organizer: Provide at least a Name for your style
- Tab: Background Select the background color
Finally click OK
Thank you. I am still doing something wrong though. I’ve used Conditional Formatting to highlight every other row in a table and I can duplicate your example in a fresh spreadsheet but I cannot get anything to work right in my data table.
My column of dollar figures is column P, 60 rows.
I Highlight my table (A5 x P60)
I go to Format> Conditional Formatting & check Condition 1, change to “Formula is” and enter “$P5=$E$1”
Then I select New Style> name it “Yellow Search”, select “Background” Tab & select yellow color square, then “OK”
And then my table has (seemingly) random lower rows highlighted yellow.
I enter search value in E1 & it highlights an incorrect row with not even one digit of my search.
I can make a random table similar to your example & do the same thing except use your exact “$B4-$B$1” and it will work as it should - I can choose any color at that step and that color works, but I can’t get my table to work for the life of me.
I Highlight my table (A5 x P60) I go to Format> Conditional Formatting & check Condition 1, change to “Formula is” and enter “$P5=$E$1”
If you highlight range A5:P60 then your formula needs to read: $A5=$E$1 - Why?
Conditional Formatting algorithm works the following:
- Start in the first cell of the range (here:
A5) - Check the condition given for this very first cell (here:
$A5=$E$1and apply style, if true - Move to next cell (whether to the right or down is not important for the explanation, so assuming next rows first cell [down]) → next cell
A6and **now the important invisible change: The algorithm adapts the formula given, just the way it would do on a copy of the formula. In consequence: The formula for the condition is automatically adapted to$A6=$E$1(relative address changes and absolute are … absolute) - Do the same for all cells
Rule to remind Provide the formula for Conditional Formatting for the upper left corner cell of the Range.