Conditional Formatting

[Worried Guy]

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.

[Tom Schreiner]

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

[SamS]

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.

[Worried Guy]

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.

[SamS]

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.

[Chris Davison]

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....

[Mark W.]

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 ]

[Chris Davison]

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 ]

[Mark W.]

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.

[Chris Davison]

sorry, so was I...

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