*Official* Excel help thread Page 2

  • Page

    of 10 First / Last

  • gang_of_bitches 1 Jun 2007 13:19:46 5,637 posts
    Seen 2 hours ago
    Registered 9 years ago
    Grunk wrote:
    Do you want the text cell that you've entered data into to change colour?

    If so I don't think you can do this by a formula alone; i.e. if you enter a formula then the result of a formula only affects the cell the formula is in, which means if you then enter data into that cell you will overwrite the formula.

    However, you can create a macro to do it, probably the easiest way to get the colour changing code is to record a macro and change the colour in one of the cells, then write a VB loop around it. Shouldn't take too long.

    Alernatively, you can have one input worksheet and one output worksheet, the input work sheet holds the data, name, chart postition etc. from this week and last week. The output worksheet, contains only formulas which change the cell colour and then you can use the offset function to place the title in the appropriate chart position.

    Good ideas. I've never actually got round to learning Macros so maybe this a good excuse.

    thanks.
  • avtar 1 Jun 2007 13:53:50 94 posts
    Seen 4 years ago
    Registered 9 years ago
    As long as there are only 3 different formats you should be able to do it with conditional formatting.

    Create a column which shows the change in position like reality_cheque's instructions. Then set the conditionals to be formula is and the enter these formulas:

    =$d4>0 for gained position
    =$d4=0 for no change
    =$d4>0 for dropped position

    where column d is the column with the calculated change in position and the cell you are adding the formatting to is row 4, adjust as necessary.

    Set the formats for each condition as you want them.

    Once you have done one cell select the cell with the conditional format then click the format painter (it looks like paint brush next to the paste icon in the toolbar) and select the other cells in the table to copy the formatting across.

    If you want more then 4 conditionals you will have to use a macro.
  • gang_of_bitches 1 Jun 2007 14:26:57 5,637 posts
    Seen 2 hours ago
    Registered 9 years ago
    avtar wrote:
    As long as there are only 3 different formats you should be able to do it with conditional formatting.

    Create a column which shows the change in position like reality_cheque's instructions. Then set the conditionals to be formula is and the enter these formulas:

    =$d4>0 for gained position
    =$d4=0 for no change
    =$d4>0 for dropped position

    where column d is the column with the calculated change in position and the cell you are adding the formatting to is row 4, adjust as necessary.

    Set the formats for each condition as you want them.

    Once you have done one cell select the cell with the conditional format then click the format painter (it looks like paint brush next to the paste icon in the toolbar) and select the other cells in the table to copy the formatting across.

    If you want more then 4 conditionals you will have to use a macro.

    cool. thanks again.
  • gang_of_bitches 15 Jul 2008 15:47:56 5,637 posts
    Seen 2 hours ago
    Registered 9 years ago
    Well its been over a year since my last stupid excel question so here goes.

    OK, I've got a spreadsheet with a column of numbers both positive and negative which I want to apply a traffic light colour coding to in order to show how far the vary from zero. So -5 to +5 would be green, -5.1 to -10 and +5.1 to +10 would be yellow and below -10 or above 10 would be red.

    So fine I think, just conditional format away, except that conditional formatting only allows for 4 conditions and this requires 5. Then I tried having a hidden column to reference where an absolute formula would make all the numbers positive and I'd just need 3 conditions, but as far as i can tell you can't have the result of a conditional format appearing in any cell other than the one it refers to so that's a dead end.

    Can anyone help?
  • opalw00t 15 Jul 2008 15:50:40 11,200 posts
    Seen 11 minutes ago
    Registered 10 years ago
    Can you just make it red text by default for that column and then apply the 3 conditional formats?

    The day charity died - NEVER FORGET

    (the mic was OK in the end)

  • gang_of_bitches 15 Jul 2008 15:53:17 5,637 posts
    Seen 2 hours ago
    Registered 9 years ago
    Ah that may be quite a neat little cheat, I'll wait and see if anyone comes up with an elegant/proper solution, otherwise I'll just do that. thanks.
  • ming 15 Jul 2008 16:00:00 275 posts
    Seen 4 months ago
    Registered 8 years ago
    You will need Excel 2007 to do this with conditional formating. If not you will need to create a Macro to change the formats.
  • opalw00t 15 Jul 2008 16:01:15 11,200 posts
    Seen 11 minutes ago
    Registered 10 years ago
    I'm good at cheating, shit at Excel :)

    Edit: OMG ming posted on the hour!

    The day charity died - NEVER FORGET

    (the mic was OK in the end)

  • Alastair 15 Jul 2008 16:05:05 16,068 posts
    Seen 1 hour ago
    Registered 13 years ago
    Select the row and choose to conditionally format as a formula rather than on a cell value.
    E.g. Select row 2 (row 1 being your headings), select Conditonal formatting, choose 'Formula is' and then put something like =$G2="Yes" (or whatever your values are)
    Then use format painter to copy the formatting the rest of the sheet.
  • L42yB 15 Jul 2008 16:06:25 1,673 posts
    Seen 4 months ago
    Registered 10 years ago
    ming wrote:
    You will need Excel 2007 to do this with conditional formating. If not you will need to create a Macro to change the formats.

    This. Write yourself a macro, it's not a very hard macro to write...
  • opalw00t 15 Jul 2008 16:09:08 11,200 posts
    Seen 11 minutes ago
    Registered 10 years ago
    And if you are as good at writing macros as I am, use my method :)

    The day charity died - NEVER FORGET

    (the mic was OK in the end)

  • Razz 15 Jul 2008 16:11:23 61,400 posts
    Seen 2 hours ago
    Registered 13 years ago
    w00t wrote:

    Edit: OMG ming posted on the hour!
    \o/ Merciless!

    --------------------------------------------------------------------
    Steam/PSN/XBOX: Razztafarai | 3DS: 1246-9674-8856
    --------------------------------------------------------------------

  • L42yB 15 Jul 2008 16:11:43 1,673 posts
    Seen 4 months ago
    Registered 10 years ago
    w00t wrote:
    And if you are as lazy about learning to write macros as I am, use my method :)

    Fixed
  • ruttyboy 1 Apr 2009 16:42:24 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    Right, this one is annoying me, so please help, I'm sure I must be missing something obvious.

    I have two worksheets, on sheet X is a table with a column of dates and one of figures. On sheet Y I'm trying to do a SUMIF so that it uses a date on the same sheet as the criteria (cell B4). For some reason it won't do it, the formula I have is:

    =SUMIF('Sheet X'!B3:B4996,">=B4",'Sheet X'!E3:E4996)

    If I replace the 'B4' with an actual typed date (ie. '01/04/09') it works fine and B4 is in standard date format so I don't see what's going on.
  • Grunk 1 Apr 2009 16:47:20 4,718 posts
    Seen 2 months ago
    Registered 10 years ago
    try changing the cell formatting of both cells to "date"

    or there's a custom format -$809 or something like that, depending on how you like your dates.

    I just tried it on my excel, works fine, 2003 I think
  • ruttyboy 1 Apr 2009 16:49:41 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    Grunk wrote:
    try changing the cell formatting of both cells to "date"

    Makes no difference :(
  • ruttyboy 1 Apr 2009 16:51:23 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    And if I remove the 'greater than or equal to' it will pull out everything for that specific date, so it seems to be the combination of the 'greater than' and the cell reference it doesn't like.
  • Grunk 1 Apr 2009 16:53:02 4,718 posts
    Seen 2 months ago
    Registered 10 years ago
    Actually tell me what you're trying to do?

    Edit: sorry got to go get my train now, I'll check back tomorrow to see if you've fixed it.
  • Mr_Sleep 1 Apr 2009 16:53:21 17,185 posts
    Seen 50 minutes ago
    Registered 13 years ago
    It could be a weird excel formatting issue, try switching formatting to general in the B row on sheet x and see what it thinks it is. It's probably just seeing it as a string of numbers and not correctly formatting it to be in line with what you have.

    Oh and why is there a comma after B4996, I would have thought that would confuse excel, it's been a while since I did fomulas though.

    You are a factory of sadness.

  • X201 1 Apr 2009 16:53:56 15,587 posts
    Seen 34 minutes ago
    Registered 9 years ago
    The formula is on sheet Y?
    The date in B4 is on sheet Y?

  • ruttyboy 1 Apr 2009 16:56:20 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    Grunk wrote:
    Actually tell me what you're trying to do?

    If you mean, can I have more detail please...

    Column of months, 01/04/09, 01/05/09, 01/06/09 etc. on worksheet Y. Next to this column I'm trying to use that SUMIF to sum everything greater than or equal to the month next to it from a table of dates and figures on worksheet X.

  • ruttyboy 1 Apr 2009 16:57:37 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    X201 wrote:
    The formula is on sheet Y?
    The date in B4 is on sheet Y?


    Yes, both the formula and date are on sheet Y.

    As I say it works fine if I either remove the 'greater than' or directly type in the date, but it won't have the cell reference for some reason.
  • Lutz 1 Apr 2009 17:02:28 48,854 posts
    Seen 1 year ago
    Registered 11 years ago
    Nothing stupid, like a space atthe end of a date in the cell your refering too?
  • ruttyboy 1 Apr 2009 17:03:42 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    Lutz wrote:
    Nothing stupid, like a space atthe end of a date in the cell your refering too?

    Nope, it's really strange. I'm gonna try it on a new sheet and see if it works.
  • ming 1 Apr 2009 17:06:19 275 posts
    Seen 4 months ago
    Registered 8 years ago
    Enter the forumula like this:

    =SUMIF('Sheet X'!B3:B4996,">="&B4,'Sheet X'!E3:E4996)
  • ruttyboy 1 Apr 2009 17:08:24 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    ming wrote:
    Enter the forumula like this:

    =SUMIF('Sheet X'!B3:B4996,">="&B4,'Sheet X'!E3:E4996)

    That got it! A thousand virtual jaffas to you Sir! Thankyou all :)
  • ruttyboy 1 Apr 2009 17:18:22 7,949 posts
    Seen 4 weeks ago
    Registered 11 years ago
    Hmmm, I don't suppose there's a way I could put an AND formula into the criteria field is there? So 'greater than this AND less than this'.
  • ming 1 Apr 2009 18:50:50 275 posts
    Seen 4 months ago
    Registered 8 years ago
    No, sumif is not clever enough for that. You can do it with sumproduct or by adding a column with the criteria test in it and then use that with the sumif.
  • Psychotext 14 Apr 2009 13:11:21 54,404 posts
    Seen 2 hours ago
    Registered 8 years ago
    Bump for help!

    Ok, so I have a sheet which summarises data from other sheets but it's becoming a pain to update it every time there's a new sheet added to the workbook. I need to try and work out if there's a better way.

    For example, on the summary sheet I have formulas which look something like this:

    March 08
    ='03-08'!B43
    ='03-08'!B44
    ='03-08'!B45


    April 08
    ='04-08'!B43
    ='04-08'!B44
    ='04-08'!B45

    ...only with considerably more data and far more complicated formulas. Is there any way to fill and change the sheet names automatically (like you would for cells). If not, is it possible to copy out the formulas to find / replace the sheet name, or even do it directly in excel?

    Thanks!
  • Twinkle 14 Apr 2009 13:46:36 542 posts
    Seen 3 years ago
    Registered 10 years ago
    A normal Excel find & replace should work, set the options to Look in = Formulas instead of values
  • Page

    of 10 First / Last

Log in or register to reply