Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MON
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6))
Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MON
I don't get two things.
Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Hi Bob, thanks, still confused but no fault of yours- I am just a beginner
with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
How about
=INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Hi, thanks for trying but I am getting way out of my depths. Will only
understand it if I can show the existing graph to some-one who can explain. "Bob Phillips" wrote: How about =INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
You could mail it to me if you want. I am off for the afternoon, but will
return in 5 hours. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi, thanks for trying but I am getting way out of my depths. Will only understand it if I can show the existing graph to some-one who can explain. "Bob Phillips" wrote: How about =INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Would be fantastic.. to what e-mail address do i send it?
Ali "Bob Phillips" wrote: You could mail it to me if you want. I am off for the afternoon, but will return in 5 hours. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi, thanks for trying but I am getting way out of my depths. Will only understand it if I can show the existing graph to some-one who can explain. "Bob Phillips" wrote: How about =INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
bob dot phillips at tiscali dot co dot uk
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Would be fantastic.. to what e-mail address do i send it? Ali "Bob Phillips" wrote: You could mail it to me if you want. I am off for the afternoon, but will return in 5 hours. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi, thanks for trying but I am getting way out of my depths. Will only understand it if I can show the existing graph to some-one who can explain. "Bob Phillips" wrote: How about =INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Can you send it again, got lost in my spam filter.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Would be fantastic.. to what e-mail address do i send it? Ali "Bob Phillips" wrote: You could mail it to me if you want. I am off for the afternoon, but will return in 5 hours. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi, thanks for trying but I am getting way out of my depths. Will only understand it if I can show the existing graph to some-one who can explain. "Bob Phillips" wrote: How about =INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Hi Bob
Have you had any luck with the sheet I sent through to you? "Bob Phillips" wrote: Can you send it again, got lost in my spam filter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Would be fantastic.. to what e-mail address do i send it? Ali "Bob Phillips" wrote: You could mail it to me if you want. I am off for the afternoon, but will return in 5 hours. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi, thanks for trying but I am getting way out of my depths. Will only understand it if I can show the existing graph to some-one who can explain. "Bob Phillips" wrote: How about =INDEX(Nationality!$B$5:$D$29,MATCH(Nationality!$A $5:$A$29,0),MONTH(date)-5) maybe extend D29 out to your final month column -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... Hi Bob, thanks, still confused but no fault of yours- I am just a beginner with these formulas. First Worksheet DATE: Aug o5 Jun Jul Aug german 20% 5% 6% French 11% 2% 8% dutch 6% 4% 2% As you type example Aug 05 next to DATE it reflects the Graph of August because SECOND worksheet has % occupancy German (and here is VLOOKUP formula as written above), French Dutch I am sure this is simple to most but I am basically working backwards from the formula to understand it and modify. "Bob Phillips" wrote: I don't get two things. Why are you using VLOOKUP with a single column, VLOOKUP works on multiple columns to pull back a value from an offset column. Why not just use MONTH(date)? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ali" wrote in message ... =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MON
Hi Still have not sorted this problem. is there anyone prepared to tackle
this problem if I email them the full graph. Thanks- address is: heinali2003 at yahoo dot co dot uk "Ali" wrote: =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6, MON
"Ali" wrote...
Hi Still have not sorted this problem. is there anyone prepared to tackle this problem if I email them the full graph. Thanks- address is: heinali2003 at yahoo dot co dot uk "Ali" wrote: =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,M ONTH(date)-6,MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? The formula =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,MONTH(date)+6)) probably isn't what you intended. The second argument to VLOOKUP should span more than one column if you have a 3rd argument that could evaluate to anything other than 1. It looks like INDEX would be more appropriate than VLOOKUP. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
You need to see the existing graph to figure out why and how it is working.
Could I e-mail it to you? "Harlan Grove" wrote: "Ali" wrote... Hi Still have not sorted this problem. is there anyone prepared to tackle this problem if I email them the full graph. Thanks- address is: heinali2003 at yahoo dot co dot uk "Ali" wrote: =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,M ONTH(date)-6,MONTH(date)+6)) Creating a worksheet with a graph that reflects nationality of visitors to a lodge. As you type the Month at the top of the worksheet, the graph reflects the said month. Previous example commenced season in July with above formula. So, if you type in July 05, the graph reads from the 1st column and July is chartered. Type Dec and a graph for december is reflected. I need to alter to commence season in June, and not July but now graph is not reading correctly. How do I alter formuala or spreadsheet that season now commences in June of the year? The formula =VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,MONTH(date)+6)) probably isn't what you intended. The second argument to VLOOKUP should span more than one column if you have a 3rd argument that could evaluate to anything other than 1. It looks like INDEX would be more appropriate than VLOOKUP. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Ali wrote...
You need to see the existing graph to figure out why and how it is working. Could I e-mail it to you? As I see it, your formula as written can't work. If you're getting anything useful out of your worksheet, it'd have to come from other formulas in other cells. Why not just show those other formulas? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MON TH(date)-6,
Okay: 2 sheets
sheet1: called Nationality Sheet2: called Graph Sheet 1 consists of a table. Column A4:20 is various nationalities. Columns B3-M3 are months of the year, with column B starting with JUL Under Month are various percentages corresponding to nationalities for that Month. Cell I1: named MONTH. format of cell is general. Cell K1 is a date. Any date can be entered, such as Dec 05. at the bottom of this page is a graph reflecting nationailies, and the graph changes DEPENDING on the date entered in K1 Sheet 2: has a table A4: A20 are nationalities B3: cell is named PERIOD and C3: cell is named YTD for the graph Formula in B4 is =VLOOKUP(1,Nationality!B4:M4, IF(MONTH(date)6,MONTH(date)-6,MONTH(date)+6)) under column YTD formula is simply = nationality!O4 Could just copy and use this existing Graph. My problem is that my year commences in JUNE and not July and if I merely change Column B3 to June it no longer registers. Does this explain in more detail. I have tried to check everywhere to see if there is a formula I am missing. "Harlan Grove" wrote: Ali wrote... You need to see the existing graph to figure out why and how it is working. Could I e-mail it to you? As I see it, your formula as written can't work. If you're getting anything useful out of your worksheet, it'd have to come from other formulas in other cells. Why not just show those other formulas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|