Posts by Kiwifinny

Re: Gantt Chart: Color Cells Dependent On Criteria

Many thanks for the direction, appreciate as always the assistance!

Hi,

have gone over historical posts and found a lot of great info and I haven't the skill to manipulate to fix my problem.
I have a list of task owners that are assigned tasks. The tasks are mapped out in Gantt chart form with time shown by an "X".
what I want to do is to colour the cells "X" with the associated cell colour of the task owner and should the owner change, the cell colour changes.

I've attached an example and appreciate any help.
Many thanks in advance.
Finny

Re: Change Operators based on Cell

Reviewed Dave's suggestion and it works perfectly, many thanks once again!

Gents & Ladies
I have used Dave's CountColor function extensively and modified it to work based on font colour. I then thought it may be useful to overlay conditional formatting however it only picks up the original formatting.

The thinking being that by applying conditional formatting the cells colour or font would change and then change the result.
I do realise that the function has to be re-calculated.
Any thoughts?
Regards John

Re: Change Operators based on arguments

Thanks Dave, unfortunately I'm either too thick to understand or haven't communicated my problem well enough. Essentially what I want to achieve is to determine which of the operators =, <, > etc are inserted between two arguments with the decision based on the result of another formula.

In the first cell I have a drop down list which is determining the operators, the result of this is <, =, > etc which refers to the "Operator Defined" area in the following formula.

I then have a nested array formula generally as
[=sum(if(Daily_Supervisors="Night",if(Production "Operator Defined" Planned,Tonnes_Produced,0),0))]

Many thanks in advance.

People and Helpers
I am trying to alter the type of operator used in one formula based on the result of an argument in a separate cell generally as follows:
If Cell 1 equals something then use the ">" operator, otherwise use the "=" operator.

I appreciate this sounds really simple and I'm having a major brain fade as I cannot figure out how to relate the two arguments as follows:
[If(Value 1 "Operator" Value2, then do something, otherwise do something else) ]
where the operator is pre-defined in another formula.

Many thanks in advance as I have had nothing but briliant results from this forum!

Re: Non standard periods, dates and date ranges

Many thanks, works well.
Thanks again.

Re: Non standard periods, dates and date ranges

Thanks for the attempt, but doesn't quite address the issue, anyone else??

Re: Drop Down Boxes

I'm not too sure what you're doing but if you want more than one result you may have to use multiple validation cells. You can copy them across multiple cells. Should you want to join them you can use the "Concatenate" function.
John

I have an issue where we feed animals in tanks a certain amount of food that is fed each day unless changed. The changed value then becomes the current value again until changed.
We have many tanks and the dates at which the changes are made are totally arbitrary.
What I am doing is recording the information and have found a way of summing the total used based on the current date and a sumproduct calc.
What I'd like to do now is to choose two dates and automatically calculate the food supplied between thos two dates which will not necessarily correlate with the dates at which the changes occurred.
Sounds a lot simpler than what it is.
Appreciate any help.
Thanks John

Re: Drop Down Boxes

You use "Data", "Validation" and then choose list. In the list box either type in the values you want in the drop down box, or refer to a range of cells that are already populated. You can also use named ranges, add error messages add information for anyone using them
Hope this helps.
John

I have a problem where feeding of animals occurs daily however periodically the amount of food is varied. This information is kept in a table with the daye of the food change. I want to calculate the food fed to that particualr group of animals, therefore I have to find the start date, calculate the number of days until it changes multiply by the daily rate, remeber this number, then continue until the next change or to the current or specified date. It's a little more difficult than it seems, or more correctly it is a little more difficult for ME!
Appreciate any help.

Re: Find selected items in a table

I would think you could either use the filter function, hence display only those rows with product for sale, alternatively you could use conditional formatting, highlighting the product that has product for sale. Additionally using the conditional formatting you could add a date and/or price definition.
Trust this helps.

Thank You

Many thanks Dave, that solved my problem exactly, you don't realise how big a help that's been.
Cheers Finny
PS: How do I make sure the thread shows solved?

Trying...

Thanks again Dave, I believe we're well on the way to a result however I get the #Value message when using this code, any other ideas wise one?

Thanks Dave

I did try that however they do not necessarily overlap. For instance all the green cells may not have the text TC2 in them, simialrly the cells containing TC2 may not all be green??

I use your "CountColor" and "SumColor" functions regualrly and now want to count or sum cells based on BOTH their colour AND their contents.
Has anybody written a function to achieve this?
Many Thanks Finny

You can alternatively use the following formulae, cumbersome though they may be, they work. If your date is in cell A1, and your separators are "." then :
=MID(A1,1,FIND(".",A1,1)-1)*1

=RIGHT(LEFT(A1,SEARCH(".",A1,FIND(".",A1,1)+1)-1),SEARCH(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1)*1

=MID(A1,SEARCH(".",A1,FIND(".",A1,1)+1)+1,FIND(".",A1,SEARCH(".",A1,FIND(".",A1,1)+1))-1)*1

I'm sure you could make it easier, but it works irrespective of number of digits.

I have a series of workbooks, all with cells located in the same place and want to reference individual cells from 18 workbooks on to a summary sheet. Although the individual workbooks cell values may change and they may in fact be closed, the summary picks up the current value. I don't want to go through and reference each cell or block of cells into the summary workbook. Any ideas?
I have the file path and file names and went to concatenate it and then use the indirect function however can't make it look at the workbook when the reference workbook is closed. Can someone please help? Many thanks!:(

All fixed, should have found that, sorry for wasting your time but it's certainly helped me.
Many Thanks