=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)>6,MONTH(date)-6, MON

[Ali]

=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)>6,MONTH(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?

 

[Bob Phillips]

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]

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]

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]

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]

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]

Would be fantastic.. to what e-mail address do i send it?
Ali

 

[Bob Phillips]

bob dot phillips at tiscali dot co dot uk

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Can you send it again, got lost in my spam filter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ali]

Hi Bob
Have you had any luck with the sheet I sent through to you?

 

[Ali]

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

 

[Harlan Grove]

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

The formula

=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)>6,MONTH(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.

 

[Ali]

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]

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?

 

[Ali]

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.