Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default how update one cell based on another cells data

Guys,

What I am trying to do is basd on the following column setup:
A B C D E
Supervisor Agent Month Score Quarter
John Doe Jane Smith September 96 3
John Doe Jane Smith December 99 4

I want to simply choose the month from from a list and have the Quarter
Column automatically choose the correct quarter.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 461
Default how update one cell based on another cells data

Try

=IF(OR(D2="January",D2="February",D2="March"),1,IF (OR(D2="April",D2="May",D2="June"),2,IF(OR(D2="Jul y",D2="August",D2="September"),3,IF(OR(D2="Octoboe r",D2="November",D2="December"),4,"No Month")))

"eraasio" wrote:

Guys,

What I am trying to do is basd on the following column setup:
A B C D E
Supervisor Agent Month Score Quarter
John Doe Jane Smith September 96 3
John Doe Jane Smith December 99 4

I want to simply choose the month from from a list and have the Quarter
Column automatically choose the correct quarter.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 353
Default how update one cell based on another cells data

=INDEX({1;2;3;4},INT((MONTH(DATEVALUE(D2&" 1, "&YEAR(NOW())))+2)/3))

"eraasio" wrote:

Guys,

What I am trying to do is basd on the following column setup:
A B C D E
Supervisor Agent Month Score Quarter
John Doe Jane Smith September 96 3
John Doe Jane Smith December 99 4

I want to simply choose the month from from a list and have the Quarter
Column automatically choose the correct quarter.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default how update one cell based on another cells data

=INT((MATCH(LEFT(C2,3),{"jan","feb","mar","apr","m ay","jun","jul","aug","sep","oct","nov","dec"},0 )-1)/3)+1
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"eraasio" wrote in message
...
Guys,

What I am trying to do is basd on the following column setup:
A B C D E
Supervisor Agent Month Score Quarter
John Doe Jane Smith September 96 3
John Doe Jane Smith December 99 4

I want to simply choose the month from from a list and have the Quarter
Column automatically choose the correct quarter.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 353
Default how update one cell based on another cells data

Actually, enter the following formula in cell E2, with the month name in cell
C2:
=INDEX({1;2;3;4},INT((MONTH(DATEVALUE(C2&" 1, 2000"))+2)/3))

It's such a habit to think the year is important, I forgot it wasn't here :-)
Makes the formula even more compact... also noticed the D2 should have been
C2 per your data.

"BoniM" wrote:

=INDEX({1;2;3;4},INT((MONTH(DATEVALUE(D2&" 1, "&YEAR(NOW())))+2)/3))

"eraasio" wrote:

Guys,

What I am trying to do is basd on the following column setup:
A B C D E
Supervisor Agent Month Score Quarter
John Doe Jane Smith September 96 3
John Doe Jane Smith December 99 4

I want to simply choose the month from from a list and have the Quarter
Column automatically choose the correct quarter.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you create 1 chart that will update based on filtered data? Diane Charts and Charting in Excel 3 August 9th 06 02:30 PM
How do I get a cell to update itself based on the month we are in? SHBmgr Excel Worksheet Functions 1 July 16th 06 07:43 PM
How do I update links based on a cell value? Vick Excel Discussion (Misc queries) 3 February 22nd 06 04:02 PM
update cell in other worksheet based... mwrfsu Excel Worksheet Functions 0 August 19th 05 05:22 PM
Update cell based on date range deversole Excel Discussion (Misc queries) 3 July 6th 05 01:58 PM


All times are GMT +1. The time now is 02:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"