Conditional Formatting & UDF

**e_lad** · 03-05-2010, 05:03 AM

Please help as I'm trying to accomplish the following:

inputing the formulat in Column F to the Conditional Formatting but it just wouldn't accept and it's throwing up an error message. I think this has got something to do with the WEEKNUM function as it's part of the Analysis ToolPak add-in. I know I can indeed leave the column as it is or perhaps hide it, but I want to embed the formula in Conditional Formatting so that if a row is inserted, the CF is carried through. Is there any other formula to get round this?

create a UDF function perhaps to display the formula as text from another cell, such as Column E to Column F

I have attached an sample file for this.

**martindwilson** · 03-05-2010, 06:10 AM

name the formula
insert/name /define
choose a name say "weeks"
in the refers to box put
=ISEVEN(WEEKNUM(Sheet1!$A4))
then use formula is = weeks in the conditional format

**DonkeyOte** · 03-05-2010, 06:43 AM

@e_lad ... I did add in a post that ISEVEN is also in the ATP and can be replaced with a Mod but given Martin's suggestion does what you want I removed said post as it wasn't relevant.

@Martin... I tried to dish the rep but it says I have to spread the love.

**e_lad** · 03-05-2010, 07:18 AM

I'm glad I found this forum...what I would do without the help from you guys.

@DO & Martin...Did you get a chance to look at the 2nd question too? I'm looking at a formula that would allow to turn the formula of the adjacent cell to a text to distplay. Doing this through Options Menu would turn the entire spreadsheet into formula!

**DonkeyOte** · 03-05-2010, 07:24 AM

You can use a UDF or you can make use of the old XLM calls via a Name

If you prefer the Name route ... create a new name:

Name: _Formula
RefersTo: =GET.CELL(INDIRECT("RC[-1]",FALSE))

In the cell you want to return the adjacent formula (immediate left) to:

=_Formula

A UDF would be more practical however

Function GETFORMULA(rngCell As Range) As String
GETFORMULA = rngCell(1).Formula
End Function

called from a cell like

=GETFORMULA(E1)

where E1 contains the formula.

**e_lad** · 03-05-2010, 07:56 AM

DO...the Name method can be quite useful as it does not requiire the macro to be enabled however, it seems to throw uip the #VALUE! error for some reason.

**DonkeyOte** · 03-05-2010, 08:16 AM

What a spanner I am ... it does help if I actually include the appropriate function within the GET.CELL call... ie 6:

RefersTo: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))

In this case you could also avoid using INDIRECT ... so if the name is be to used say in F2 to relate to E2 then with F2 active cell define the name as: