• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. T

    Conditional Formattin

    Make sure A1 is the active cell in your range when you apply the conditional formatting. I think the problem is that B30 was the active cell and then you should enter the formula as =AND($A30=0;$B30-0). It does work in OpenOffice.
  2. T

    Faster Calculations (By Making VB Code Shorter)

    I don't think so. But it will probably get faster if you don't select cells to perform an action. For example, the last two lines of your code sample could be written as: Range("Z9").ClearContents
  3. T

    Break a Whole Number in to Billions, Millions, Thousands, Hundreds, Tens Ones

    If your number is in B1: in B2: =MOD(B1,10) in B3: =MOD(B1,100)-B2 in B4: =MOD(B1,1000)-sum(B2:B3) etc.
  4. T

    Yet another issue of alternating colors

    If you really have A,B,C,D etc in column A (but that is probably not the case), you could use: =MOD(CHAR(A2);2)=0 as formula for the conditional format. Maybe there is a similar option for your real data?
  5. T

    Separating contents of a column into two columns.

    You can use text-to-columns with colon and comma as separators.
  6. T

    Working with the AND function in combination with IF(AND()) in a Gantt chart.

    I think I know understand what you are trying to do: the duration is in workdays. Try this: =IF(AND(Q$23>=$O26;Q$23<=WORKDAY(O26;N26-1;$HO$22:$HO$46));IF(WORKDAY(O26;N26-1;$HO$22:$HO$46)*$HI$27>Q$23;$HI$26;"");"") It will still plot a character for the weekends/holidays, but the...
  7. T

    Working with the AND function in combination with IF(AND()) in a Gantt chart.

    I think you want the CONCATENATE function instead of AND: =CONCATENATE(IF(AND(Q$23>=$O26;Q$23<=$O26+$N26);IF($O26+$N26*$HI$27>Q$23;$HI$26;"");"");WORKDAY(O26;N26-1;$HO$22:$HO$46))
  8. T

    How to detect formulas in cells (without VBA)

    Cyrilz, If I understand correctly you have a cell with a formula that gives 0 when a task is done, but sometimes the manager types a zero there if it is done before the formula says so? If that is the case, maybe you can make the formula return a small number (0.01 for example) and format it...
  9. T

    VLookUp currious behaviour

    The result of the Left() formula will be text, your list probably contains numbers. Use Value(Left(P5,4)) to change the text into a number.
  10. T

    Stop Proportionatly Scaling Bar Graph

    It could be that when you make the plot area smaller, Excel is changing the scaling on the axis, thereby changing the bar lengths.
  11. T

    Trans Workbook Equations

    You can use the INDIRECT()-function: =INDIRECT(A5&"!$B$4")
  12. T

    column chart w/ 2 axes

    You can't move just one point of a series to the secondary axis. Maybe you could make a separate chart for the YTD values?
  13. T

    Long time reader! First time poster. (conditional formatting hurts me)

    > Btw you can hide the cell contents much more elegantly using custom cell format code ;;; If you do that, you will also have to set a different number format in the CF-dialog, or the balls will stay hidden, even though they are now green!
  14. T

    Data in columns to rows..

    Add Name and Product to the row-area and the week columns to the data-area.
  15. T

    Data in columns to rows..

    I'd suggest using a Pivot-table for this.
  16. T

    Subtract From two Columns and Total the difference

    If I understand correctly you want to sum all the differences when the value in column K is bigger than in H? The easiest way is probably to use a helper column and first calculate the differences in column L with max(K2-H2,0) and then sum that.
  17. T

    Create formula for an entire column

    You could use a table and protect the sheet against inserting rows.
  18. T

    Conditional formatting basics

    You can use the following in the conditional formatting dialog: 1) between today()+1 and today()+7 2) equals today() 3) less then today()
  19. T

    Difference in Timestamps

    I would use networkdays to get the days and an extra formula for the time. If your dates are in column A you would get: for the days: networkdays(A1,A2)-1 for the time: if(day(A1)=day(A2),A2-trunc(A2)-(A1-trunc(A1)),A2-trunc(A2)+1-(A1-trunc(A1))) That is, if you want to count all 24 hours...
  20. T

    SWOT analysis bubble chart?

    A bubble chart requires 3 values for each point: x, y and bubble size. So you need to add x and y values. The chart in the picture uses size/scale as x-values and relevance/probability as y-values. If you choose negative and positive values as is done in the chart, everything should end up in...
  21. T

    Error in macro - Index refers

    Dee, To me, that error sounds like the worksheet is not present in the workbook you refer to. Just before the line with the error, you define names for two workbooks: Ftwbook and Thiswbook. But on the line with the error you refer to ThisWorkbook. Are you referring to the correct workbook...
  22. T

    Date reminders/alerts

    replace the ";" with a "," (I have the dutch version, which uses "," as a decimal separator and thus uses ";" to separate arguments in formulas)
  23. T

    Credit Book Project

    Here's how a one-sheet approach (as mentioned in your other thread) would work: You have 1 sheet for data, with columns for name, date, description, amount (including +/- for debit credit) On the summary sheet you would then use either formulas or a pivot table to summarize the date. example...
  24. T

    Date reminders/alerts

    =AND(NOT(D5="");D5<=NOW()-7)
  25. T

    Extracting Whole Address to Multiple Cells

    Instead of using left(),right() etc, you can use Text to Columns to split on the colon, then once more on the City/State part and split on 'space'.
Back
Top