Conditional Formatting

Worried Guy

New Member
Joined
May 11, 2002
Messages
6
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 <phew>, each time the drop-down list date in the first spreadsheet is changed, it checks those values in the cells in the second sheet.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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 <phew>, 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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
sorry, so was I...

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

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top