Conditional Formatting #3

Hi all

I have this conditional format in cell BB35
=IF(BB35>$BB$37,TRUE)

If I insert cells between BB35 & BB37, then the
formula changes to =IF(BB35>$BB$38,TRUE)

Is there a way of making BB37 absolute,
or is there a formula using ROW that I could use?

Many thanks

-- 
George Gee
--


0
10/23/2004 11:34:14 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
562 Views

Similar Articles

[PageSpeed] 30

Select cell B35
Choose Format>Conditional Formatting
 From the first dropdown, choose Formula Is
In the formula box, type:  =B35>INDIRECT("$B$37")
Click the Format button and select your formatting
Click OK, click OK

George Gee wrote:
> I have this conditional format in cell BB35
> =IF(BB35>$BB$37,TRUE)
> 
> If I insert cells between BB35 & BB37, then the
> formula changes to =IF(BB35>$BB$38,TRUE)
> 
> Is there a way of making BB37 absolute,
> or is there a formula using ROW that I could use?

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/23/2004 12:13:08 PM
The formula should be:    =BB35>INDIRECT("BB37")

Debra Dalgleish wrote:
> Select cell B35
> Choose Format>Conditional Formatting
>  From the first dropdown, choose Formula Is
> In the formula box, type:  =B35>INDIRECT("$B$37")
> Click the Format button and select your formatting
> Click OK, click OK
> 
> George Gee wrote:
> 
>> I have this conditional format in cell BB35
>> =IF(BB35>$BB$37,TRUE)
>>
>> If I insert cells between BB35 & BB37, then the
>> formula changes to =IF(BB35>$BB$38,TRUE)
>>
>> Is there a way of making BB37 absolute,
>> or is there a formula using ROW that I could use?
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/23/2004 12:19:17 PM
Debra

Spot on!

Many thanks

George Gee


*Debra Dalgleish* has posted this message:

> Select cell B35
> Choose Format>Conditional Formatting
>  From the first dropdown, choose Formula Is
> In the formula box, type:  =B35>INDIRECT("$B$37")
> Click the Format button and select your formatting
> Click OK, click OK
>
> George Gee wrote:
>> I have this conditional format in cell BB35
>> =IF(BB35>$BB$37,TRUE)
>>
>> If I insert cells between BB35 & BB37, then the
>> formula changes to =IF(BB35>$BB$38,TRUE)
>>
>> Is there a way of making BB37 absolute,
>> or is there a formula using ROW that I could use?
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html


0
10/23/2004 12:28:53 PM
Debra

Yes, I did have the sense to alter that!

Many thanks

George Gee



*Debra Dalgleish* has posted this message:

> The formula should be:    =BB35>INDIRECT("BB37")
>
> Debra Dalgleish wrote:
>> Select cell B35
>> Choose Format>Conditional Formatting
>>  From the first dropdown, choose Formula Is
>> In the formula box, type:  =B35>INDIRECT("$B$37")
>> Click the Format button and select your formatting
>> Click OK, click OK
>>
>> George Gee wrote:
>>
>>> I have this conditional format in cell BB35
>>> =IF(BB35>$BB$37,TRUE)
>>>
>>> If I insert cells between BB35 & BB37, then the
>>> formula changes to =IF(BB35>$BB$38,TRUE)
>>>
>>> Is there a way of making BB37 absolute,
>>> or is there a formula using ROW that I could use?
>>
>>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html


0
10/23/2004 12:30:00 PM
You're welcome, and I'm glad you're more awake than I am!

George Gee wrote:
> Debra
> 
> Yes, I did have the sense to alter that!
> 
> Many thanks
> 
> George Gee
> 
> 
> 
> *Debra Dalgleish* has posted this message:
> 
> 
>>The formula should be:    =BB35>INDIRECT("BB37")
>>
>>Debra Dalgleish wrote:
>>
>>>Select cell B35
>>>Choose Format>Conditional Formatting
>>> From the first dropdown, choose Formula Is
>>>In the formula box, type:  =B35>INDIRECT("$B$37")
>>>Click the Format button and select your formatting
>>>Click OK, click OK
>>>
>>>George Gee wrote:
>>>
>>>
>>>>I have this conditional format in cell BB35
>>>>=IF(BB35>$BB$37,TRUE)
>>>>
>>>>If I insert cells between BB35 & BB37, then the
>>>>formula changes to =IF(BB35>$BB$38,TRUE)
>>>>
>>>>Is there a way of making BB37 absolute,
>>>>or is there a formula using ROW that I could use?
>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/23/2004 12:31:23 PM
Reply:

Similar Artilces:

Data on a 3.5 diskette
I realize this may be somewhat old school, but I have a problem with some data on a 3.5 diskette using WinXP. I put some personal data in a '.xls' file on a 3.5 diskette and update it every now and then. The other day when I put the disk in to enter some new data, I received the following error msg: "book1.xls" cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. My options at this point are "RETRY" or "CANCEL". I check the...

Not enough system resources to display completely #3
Hi, We often get this "Not enough system resources to display completely" in Excel, using Office 2003, on PC's with sufficient Memory and also various display drivers.... After a restart, it useally works, but it appears to be a memory bug somewhere in Office 2003. Any ideas ? Peter ...

Help!!!!! Problem with an upgrade from CRM 1.2 to 3.0
I was upgrading from CRM 1.2 to 3.0 when my server ran out of space due to the log file growing was to big. This crashed the install. I fixed the log file issue and went to try to redo the upgrade. The issue came into play that while I was doing the intial upgrade to 3.0 my Crystal db was removed and now any time I try to upgrade to 3.0 I get a malformed xml error with the crm database. I have the MSCRM and METADATA db's backed up so I have all my companies data but now I need to finish the upgrade. Can anybody help? Thanks, J When I've done upgrades I've found the key thing i...

Conditions in macros
Can anyone let me know why Access insists that conditions in macrosdon't seem to support comparitive operaters for checking ranges, andonly support the Between.. And operators? I had a whole set ofconditions where some were checking between a range of numbers (suchas Age). Where I had 20<=Forms![Form1]![Age]<25, it was ignoring thecondition, but it did work when I changed it to (Forms![Form1]![Age]Between 20 And 25).Cheers,Chris For compound conditions, use this kind of construct: ([Forms]![Form1]![Age] >= 20) AND ([Forms]![Form1]![Age] <= 25)-- Allen Browne - Microsoft MVP...

Circular Reference #3
One of my works book keeps telling me there is a circular ref somewhere. I used this: Sub FindCirRef() Dim RefCell As Range Dim Counter As Long For Counter = 1 To 4 Set RefCell = Worksheets("Sheet1").CircularReference RefCell.Copy Worksheets("Sheet2").Cells(Counter, 1).PasteSpecial Paste:=xlPasteFormulas Worksheets("Sheet2").Cells(Counter, 2).Value = CStr(RefCell.Address) RefCell.ClearContents Next Counter End Sub to locate the ref. but it says that in line "Paste:=xlPasteFormulas" there is a syntax error. I do not know VBA , what do ...

downloading problems #3
I have Money 2005 and have downloaded bank data without any problems for 2 years. All of a sudden I get an error message. I have already tried a quick file repair and a standard repair and neither has solved the problem. In microsoft.public.money, rmos wrote: >I have Money 2005 and have downloaded bank data without any problems for 2 >years. All of a sudden I get an error message. I have already tried a quick >file repair and a standard repair and neither has solved the problem. If http://www.microsoft.com/money/bankonline.aspx . indicates data for your bank is "prov...

Integration assistant for excel #3
I am trying to run an integration and when I try to map the detail query information, my fields are not coming in to select from. The fields come in okay for the header query. Is there a step that I am missing? Thanks, Laura Laura: Here is KB solution you need to review: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;843786 You need to get the list of key fields right. "LauraS" wrote: > I am trying to run an integration and when I try to map the detail query > information, my fields are not coming in to select from. The fields come in > ...

VBA Question #3
Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing t...

Conditional Printing of Certain Fields in A Report
i need to have a conditional printing of some fields (SEE BELOW) VENDOR SHARE $999.99 LESS TAX $999.99 country ENGLAND tax (%) 20% AMT DUE $999.99 how do I print only these 5 lines when the tax amount is not zero. Also how do i combine line 2,3 & 4 and looks like LESS ENGLAND (20%) TAX instead. I tried doing the following: ="LESS" & [COUNTRY] &"(" &[TAX (%)] &")" in the control source but i got a #ERROR error Any help would be appreciated. Kevin T <Kevin T@discussions.microsoft.com> wrote: >i need to...

how can i apply conditional formatting by code ?
Say that there is a table T_PERSONAL [Name (text), Surname (text), Salary (integer)]. I created a Tabular Form (F_PERSONAL) which retrieves information from T_PERSONAL. I want to put a image for a specific record on the form, if the record satisfy some specific criteria. For other record which does not satisfy the criteria, image should be invisible. Say that, if [Name (text)] ~text13~ is "ALBERT", Image Object ~Image13~ should be visible. I dont suppose that Image Object should be included in T_PERSONAL. It will be inserted manualy during design stage of Tabular Form. I would not as...

setup #3
If i wanted my echange server to connect and pick up my email at www.freedomnames.co.uk how would i do it? Many Thanks Joe ...

formatting #4
Hi, is it possible to automatically display those cells with a formula with a different format. Eg I would like 'input' cells in green, and 'calculated' cells in red. Thanks. Change color numbers to suit Sub colorcells() Dim c As Range For Each c In Selection If c.HasFormula Then c.Interior.ColorIndex = 4 Else c.Interior.ColorIndex = 6 End If Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message news:ONIaRcClJHA.5732@TK2MSFTNGP05.phx.gbl... > Hi, i...

Copying the column heading into all non-empty cells #3
Gord you're a genius! Thanks so much - that works a treat! Sorry I didn't get back to yo last night - I'm in London and it was past my bedtime. The reason I'm copying the headings into the rows by the way, is that have a database which I want users to be able to search, and it's easie if I just have have one column listing all the Amenities for each hotel rather than 120. Incidentally, my next move is to now merge all the cells in each ro into one, with the amenities separated by spaces. I've just downloade a "Merge Cell Wizard", which has accomplished th...

date format #18
Hi all, I have a spreadsheet containing lots of pivot tables. The source data contains date formulas in the following ways: =IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11 =IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07 As a result, when I try to sort my pivot table data by date, the dates that are shown as Nov-07 are recognised as text therefore only allowing me to sort by alphabetical order. The dates that are like 2007-11, i am able to sort it by the earliest/latest dates. I...

Copy worksheets and formats
Hi, I have a worksheet (budget) set up as 31 columns representing days of a month. I also have a worksheet designated as yearly, 12 columns for each month. The first worksheet, Jan, totals numerous rows and sends the data to the Jan column of the monthly WS. I need to copy the Jan worksheet in a way it will change the Jan! to Feb!, then Mar!, etc. If I copy it as is the data will be the same as the month of Jan. Hope you can understand this. I can't ;+) Ken More than one way, but an easy way if each month formulas are the same is to copy Jan sheet, and paste them into the Feb a...

file format needs to be changed
I have an excel file which comprises of a template for Identity card with a jpg file embedded in it. I would like to save this file in a jpeg format so as to get a photo printout of the same. How is this possible? If you scan the document, that would put the file into an image format. Then you could convert the file to whatever image file format you wanted. Just a thought... Highlight the area you want printed then hold SHIFT key and select Edit>Copy Picture. Open your favorite graphics editor or MS Paint and paste to there. Save as *.jpg Gord Dibben Excel MVP On Tue, 14 Jun 200...

user form
I am so frustrated. I am creating a form and I want to force a phone number format. Easy in Excel but cannot find it anywhere in word 2007. Please help Thanks Use the VBA Format function Format(1234567890, "(###) ###-####") will return (123) 456-7890 -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Dcook" <Dcook@discussions.microsoft.com> wrote in message news:5F0E1C93-E828-46F7-98EF-FE6DF84...

OWA login #3
On OWA Forms Base Authentication login, is there a way to have the domain already in the box, that way all my clients would have to worry about is their username and password. Thanks You'd probably be able to customise the logon.asp page to do this, but I would expect it to be overwritten by future service packs, so make a copy after your modifications. -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Walt" <anonymous@discussions.microsoft.com> wrote in message news:116fb01c441c0$4d66de50$a301280a@phx.gbl... >...

Track 3 Budget Accounts in Money 07
Here is my situation. My wife and I have personal checking accounts at our bank. We therefore fund a third account at the same bank for Household expenses. All of the accounts are linked to a savings account. Is there a way to set up 3 separate budget accounts for myself, my wife and for the household without creating separate money (*.mny) files for each? Money Budgets are systemic: they cover all expense/income across all (or a specified subset) of accounts. This way they track spending regardless of the account used to do it. This is probably not what you want but most people find i...

Sort data and leave one specific cell with $ dollar sign formatting
In Excel 2003, I have revenue numbers going from b2 to b100. I want to make sure B2 always has a dollars sign, comma, and two decimal places (example: $5,348.25). All other cells have same formatting but not the dollar sign. I want to sort in ascending or descending and B2's format never change regardless of what number is in it. Tried several conditional formatting with IF statement and Text function but could not get it to work. "mary" <marydavisjones@yahoo.com> wrote in news:1122823657.833929.252400 @g49g2000cwa.googlegroups.com: > In Excel 2003, I have revenue...

E-mail Templates HTML format with pictures
Is it possible to create custom templates with an HTML body, including like my company logo ? If this is possible, can any one help me.. Thank you again in advance I created a web page in MS FrontPage. Posted the page on a web server with a public address. Then copied the page into a Direct Email Template. If you don't require a logo you can just paste it from FrontPage right into the Direct Email Template. HTH Donnie Sweat "Ulrich" <Ulrich@discussions.microsoft.com> wrote in message news:A2BF4B7E-D92E-41EC-A55A-7B53AEE60A22@microsoft.com... > Is it possible ...

Stock Symbols #3
I asked this question yesterday and did not get a "workable" answer. So, let me please try again... In my portfolio I have two identical mutual fund accounts. One is an IRA account. MS Money will only allow me to have a stock symbol for one of the two accounts. When I try to add a symbol for the second account Money tells me the symbol is already being used. I list the two funds as "XXX Fund" and "XXX Fund (IRA)" but only one has a symbol in the portfolio. When I update online I have go back in a do a manual update for the fund without the symbol. ...

Opening Excel Spreadsheet on a tab #3
Not really what I am looking for. As I will be displaying differen sheets depending on what link is clicked, I don't need to have default sheet, instead I need to specify a different sheet to b displayed before excel is opened. i.e. A link for the prices sheet opens the spreadsheet on the price sheet, and straight after there is a link to the sales sheet, whic opens the spreadsheet on the sales sheet on the spreadsheet. Hope that makes more sense, Dav -- david.willi ----------------------------------------------------------------------- david.willis's Profile: http://www.excelfor...

CRM 3.0 Problem Adding License
I am trying to add a NEW license to an existing 10U CRM 3.0 Pro server installation. I got that key from our MSDN subscription. We are a Gold Certified partner. However, the License Manager has DELETE and UPGRADE licenses both grayed out and I cannot add any new licenses. Each time I try, I get the error "a server license has already been added". I can't even delete the existing license to add a new one. I have tried closing all client connections and disabling the server and that too makes no difference. I have also tried to register, assuming that these options may ...

Locking Formulas #3
Is there a way to lock formuals in a worksheet so that users can enter new data without accidently dleleting the formulas? You may unlock the desired cells and then protect the worksheet. See "protection" in Help. Lisa wrote: > Is there a way to lock formuals in a worksheet so that users can enter new > data without accidently dleleting the formulas? There are two aspects to this: a. Each cell can be locked or unlocked - this is controlled through Format | Cells | Protection tab, and the default setting is Locked. b. The worksheet can be protected (and this can have a ...