Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Conditional Formatting

  1. #1
    New Member
    Join Date
    May 2002
    Location
    England
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've been landed with a stinker of a project by my boss with an even worse deadline.
    I've got two major problems.
    1. I have two cells, one is a drop-down list with numeric values 1-12 and the other is a blank cell that I want to change automatically with the selection of the first cell. It should be of date form with 1=April 2002 through to 12=March 2003 (Financial Year)
    2. The next problem I have is that I want to link two spreadsheets so that the first sheet updates values in the second and the second spreadsheet looks to the first to check what month is selected so it can update the values in the correct cells. In addition to this , each time the drop-down list date in the first spreadsheet is changed, it checks those values in the cells in the second sheet.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't worry, Worried.
    From what little I can gather, you don't have a big problem here.
    Please list as many details as possible for a more detailed reply.
    Tom

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 13:51, Worried Guy wrote:
    I've been landed with a stinker of a project by my boss with an even worse deadline.
    I've got two major problems.
    1. I have two cells, one is a drop-down list with numeric values 1-12 and the other is a blank cell that I want to change automatically with the selection of the first cell. It should be of date form with 1=April 2002 through to 12=March 2003 (Financial Year)
    2. The next problem I have is that I want to link two spreadsheets so that the first sheet updates values in the second and the second spreadsheet looks to the first to check what month is selected so it can update the values in the correct cells. In addition to this , each time the drop-down list date in the first spreadsheet is changed, it checks those values in the cells in the second sheet.
    It seems that you need a vlookup table - if you are using a combo box you will have a Cell Link which returns the row of matching data. Because you using numbers starting at 1 the matching row number also happens to be the value you want to find in your lookup table. Add the dates to the right of your numbered list, name the table eg MyDates and in the cell where you want the date to appear use =vlookup(cell link,Mydates,2,false). Remeber to format your cell as a date and it should work. The second sheet simply can reference your lookup.

  4. #4
    New Member
    Join Date
    May 2002
    Location
    England
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tom,

    The first issue concerns two cells in Sheet1
    The first cell has a data validation and a drop-down list with values 1 to 12 showing the months of the financial year.

    I want a second cell to show the month in text format based on the value of the first cell. (1=April, 2=May, etc) I've tried the nested 'IF' function but it only allows me to nest 7 different conditions and I need 12.

    The second problem I have is that the numeric data that is entered onto sheet1 needs to be stored on another hidden sheet (sheet2!) so that when a user changes the month, any data already input on sheet1 is saved to sheet2 without any prompting.

    If the month is changed in sheet1, then the data input cells in sheet1 automatically look at the corresponding data in sheet2 and displays that value in the original cells. sheet2 also needs to reference sheet1 to ensure that it updates the correct data in each section (by month) as the month is changed. I know, I'm making it unnecessarily complex but if you shed some light on what formulae I should be looking at then I'd very much appreciate it.

    Thanks,

    Worried Guy.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Worried - the formula for the lookup table is the same if you are using validation rather than a comb box.

    Your second requirement is a little unclear, if you want to email your spreadsheet it would make it easier to see what you are really after.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 14:47, Worried Guy wrote:
    (1=April, 2=May, etc) I've tried the nested 'IF' function but it only allows me to nest 7 different conditions and I need 12.
    Guy.
    replace your nested IF statement with :

    =VLOOKUP(the cell,{1,"April";2,"May";3,"June";4,"July";5,"August";6,"September";7,"October";8,"November";9,"December";10,"January";11,"February";12,"March"},2,0)

    "Sending" the info to a page dependant on the month may be a bit trickier though, sounds like a job for our VBA experts....


    :: Pharma Z - Family drugstore ::

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 15:37, Chris Davison wrote:


    replace your nested IF statement with :

    =VLOOKUP(the cell,{1,"April";2,"May";3,"June";4,"July";5,"August";6,"September";7,"October";8,"November";9,"December";10,"January";11,"February";12,"March"},2,0)
    Or perhaps...

    =TEXT(MOD(A1+2,12)+1&"/00","mmmm")

    ...at any rate CHOOSE would be preferrable to VLOOKUP...


    =CHOOSE(A1,"April","May","June","July","August","September","October","November","December","January","February","March")

    [ This Message was edited by: Mark W. on 2002-05-12 16:31 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sheeesh, I totally missed the years on mine.... *memo to myself to pay attention*

    Mark - can yours flip the year up by one when it hits January ?

    [ This Message was edited by: Chris Davison on 2002-05-12 16:31 ]

    [ This Message was edited by: Chris Davison on 2002-05-12 16:33 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 16:29, Chris Davison wrote:
    sheeesh, I totally missed the years on mine.... *memo to myself to pay attention*


    [ This Message was edited by: Chris Davison on 2002-05-12 16:31 ]
    Try it again... I was editing my post and briefly messed it up.

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry, so was I...

    yours is obviously better, but the year increases when January is reached...(financial years) is this possible ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •