Search results

Hello All, I'm a big fan of sumproduct formula but I'm a bit stuck with this one. I have a column of persons from a database in column A, representing people in teams. I have a list of people in one of the teams in column B I want to count the number of time the people in column B apperas...

That's really nice, a bit annoying for the end user but really nice ! I'll keep this in mind :-) Thanks a lot.

Try `=SUMPRODUCT( --((MONTH(A5:A125)=1)*(YEAR(A5:A125)>1980) )

@Hui : that was my achievement a few minutes ago... Thanks a lot for your help. :-)

@Hui: I have a column for Task allowed, and one for Task days remaining. beside these 2 columns, I have a column for each week (see my submission next week) counting days remaining per week, each cell contains a formula like ={prevCellLeft} - {DaysSpentCell}, each week. Task completion can...

@Hui : You're totally right. Let me explain a bit about this : My IT Guys install Office without allowing Macros by default, leading to the annoying and confusing secutity message. They don't want to allow some directories to automatically accept macors (which I can undestand), so my users...

@VaraK : Thanks wasn't aware of this HasFormula function... I'll give it a try @OldChippy : Forgot to say I need a formula in my cell to detect if another cell is a formula... :-)

Hello, I want to conditional format cells that contains no formula in a table (all other cells contains formulas). This will help me finding breaks in my Still-to-do lists. I cannot use VBA (otherwise I'd tested against range.formula() in a function. Any thought ?

Hello I assume your value is in the cell I2. change the formula to =if( I2>=0, "p", "q") With 2007+ : Select your cells, then click on then Conditionnal "Formating button" in "Style group" / "Icon Set". You'll see there 3 arrows "up green", "even yellow", and "down...

@ptwuk : You DO NOT need to add several subtotal column, since the SUBTOTAL check for the visibility of the line. This function (subtotal on column B) will return 0 in all the following cases : - autofilter in column B that hide the line - autofilter in any other column that hide the line...

If you have sheet names in one column (or at least something you can rely on to determine the name of the sheet) you can use the INDIRECT() function like this (assuming B3 is the sheet name : = INDIRECT( "'" & B3 & "'!somecellref" ) This formula will give you the...

There is a post by Chandoo on filtering the data here : http://chandoo.org/wp/2010/05/11/exclude-hidden-rows-from-totals/ Use a column with subtotal formula, and add it to your formula as the "Autofilter criteria". Tada. ...

hello again, it seems to me nobody as an idea :-) Like Chandoo, I'm (re-)learning Access (in fact actualizing my knowledge) :-) Cyril

Hello all, I have the following table and I want to summarize it in a Pivot Table, adding some calculated elements. Item Function Status Item1 Func1 OK Item1 Func2 NOK Item2 Func3 {empty} I have created a Pivot Table like this # Status OK NOK...

Yes, First point of the tweetboard, I want the part Calculs!F38 and Calculs!E38 to be bold. F38 replaces with "retard" / "avance" E38 contains a number.

Hui, I'm afraid not, since it is for the tweeboard of my Gantt XLS table.

Many thanks to both of you. You know how much I avoid macros each time I can (dumb users afraid of "Macro" warning) but this time I cannot figure how to bold part of a cell within a macros ? Cyril.

Hello all. When i enter Some text in a cell I can Bold or underline some part of it. My question is : Is it possible in text calculated by a formula ? I Want to highlight status in The tweetboard in my gantt xls file

Nagowing, You can also use the "Paste Special" menu when copying, and click on "Paste link..." button. You will end up with cells containing directly the link instead of absolute formulaes.

@Hui... Never thought I'd use INDIRECT with that level of split. :-) @Mike My guess is you also need OFFSET function to select the data in the sheets. That way you can refer only to sheetNames, directly from the master workbook. Another way is to use "external data" links in sheets in the...

@Chandoo & @Hui : Many many thanks to both of you. You are opening me new sights. I've never thought about number formating but this will be great for both 1/ correcting the bug and 2/ improving speed. I've been largely inspired by Fernando's work for the Gantt diagram and tweetboard...

Hui, Did you mean the behaviour of my worksheet ? If yes --> I use XL2007 iferror() function regularly in my sheets, so I don't expect it to work in 2003. If not --> I add the IF test because I suspect the function xill be called each time a cell changes.

Hello Hui, thanks for your kind comments, it's always a pleasure to hear this from you (and Chandoo of course :-) Nice idea, but I've avoided macros from now on (apart the DonneSemaine function). But I'll give this a try, adding the following test : If Target.Address() = "$N$5&#34...

Here it is : http://dl.dropbox.com/u/4900864/ganttSample.xlsm Note : I removed all sensible data ;-)

Hello Hui, and thank you. P1 contains "Day" and Q1 contains "week" since they are used for data validation (list) in N5. I've changed the 3rd line to format Number (0 decimal). Nothing changes. But I've noticed that wherever I refresh a cell correct formating appears. Could this be because...