Find Column Number via Lookup
 

Hi All,

I have two sheets.

1) columns with days & a Month ending total column
Similar to:
4/1/07 4/2/07 4/3/07...4/29/07 4/30/07 APRIL 07 TOTALS

2) Lookup totals by MONTH
Simliar to:
First, I pick the MONTH I want to look at
APRIL
MAY
JUNE, etc

Then, the columns look-up by MONTH name, what the totals are based on
a list that gives the 'column numbers' in the array that it should
look at. (i.e., column number 28 for April, 57 for May, etc)

This is fine, for one month, however I want to display the CURRENT
month (month selected above) and the PREVIOUS month (Month selected -
1 cell in the 'list') I was trying to find the formula to say "Look
at the month from the drop-down & then go to the list & select the
cell above the month I selected", in lay-man's terms.


Also, I have to manually update the column numbers when I get more
months (I haven't pre-set the column dates for every month in 2007),
how do I find the column number in Sheet 1, by looking up the 'label'
of the "APRIL 07 TOTALS" in sheet 1 & updating it on the list in Sheet
2?

Let me know if this is unclear.

Thanks for your help!!!
Ryan

Find Column Number via Lookup
 

Let me know if this is unclear.

Does the number of replies you received mean anything? <bg

I guess this is related to your other post from a few days ago?

I know what you want to do but I can't tell where all this stuff is from
your description. If I could actually see it I could figure it out (that's
what I do!).

Biff

"Ryan" wrote in message
oups.com...
Hi All,

I have two sheets.

1) columns with days & a Month ending total column
Similar to:
4/1/07 4/2/07 4/3/07...4/29/07 4/30/07 APRIL 07 TOTALS

2) Lookup totals by MONTH
Simliar to:
First, I pick the MONTH I want to look at
APRIL
MAY
JUNE, etc

Then, the columns look-up by MONTH name, what the totals are based on
a list that gives the 'column numbers' in the array that it should
look at. (i.e., column number 28 for April, 57 for May, etc)

This is fine, for one month, however I want to display the CURRENT
month (month selected above) and the PREVIOUS month (Month selected -
1 cell in the 'list') I was trying to find the formula to say "Look
at the month from the drop-down & then go to the list & select the
cell above the month I selected", in lay-man's terms.


Also, I have to manually update the column numbers when I get more
months (I haven't pre-set the column dates for every month in 2007),
how do I find the column number in Sheet 1, by looking up the 'label'
of the "APRIL 07 TOTALS" in sheet 1 & updating it on the list in Sheet
2?

Let me know if this is unclear.

Thanks for your help!!!
Ryan

Find Column Number via Lookup
 

Hi Ryan

Hopefully you haven't gone too far with this project to date.
If you are trying to input data for each day of the month in a separate
column, then have a column for the total for the month followed by
columns for each day of the succeeding month, followed by a total
column, then you will not be able to accommodate a full year of data -
unless you are using XL2007.

Earlier versions of XL have a limit of 256 columns. 365 (or 366) days
plus 12 monthly totals = 377

Also, I cannot see how your April total could be column 28. If you start
with 01 Apr 07 in column A, the 30 Apr 07 would be in column AD which is
column 30, and therefore April Total would have to be in column 31 - not
28.

I think you will need to undertake a fundamental re-design of your
layout.

If you post back with details of what you are trying to achieve, what
data you have etc. then we may be able to offer you further advice.


--
Regards

Roger Govier


"Ryan" wrote in message
oups.com...
Hi All,

I have two sheets.

1) columns with days & a Month ending total column
Similar to:
4/1/07 4/2/07 4/3/07...4/29/07 4/30/07 APRIL 07 TOTALS

2) Lookup totals by MONTH
Simliar to:
First, I pick the MONTH I want to look at
APRIL
MAY
JUNE, etc

Then, the columns look-up by MONTH name, what the totals are based on
a list that gives the 'column numbers' in the array that it should
look at. (i.e., column number 28 for April, 57 for May, etc)

This is fine, for one month, however I want to display the CURRENT
month (month selected above) and the PREVIOUS month (Month selected -
1 cell in the 'list') I was trying to find the formula to say "Look
at the month from the drop-down & then go to the list & select the
cell above the month I selected", in lay-man's terms.


Also, I have to manually update the column numbers when I get more
months (I haven't pre-set the column dates for every month in 2007),
how do I find the column number in Sheet 1, by looking up the 'label'
of the "APRIL 07 TOTALS" in sheet 1 & updating it on the list in Sheet
2?

Let me know if this is unclear.

Thanks for your help!!!
Ryan

Find Column Number via Lookup
 

Hi All,

I appreciate your replies. I realize that the number of cells vs days
in the year + weekly & monthly totals are more than the allotted
amount in excel. We only do business certain months of the year, so
it's less than A:HL (thank goodness!). Also, it's column number 28
due to the range I selected; I have a some labels in the first few
columns of the spreadsheet which throws off my lookup if I select
them.

I figured out how to search for the text of the Month label (April 07,
May 07, etc).

My only outstanding question is:

I choose a Month from my drop-down list (Validation) & I have a
VLOOKUP looking up the month from the validation list & pulling the
value from the selected column in a VLOOKUP (formula below). However,
I want to show "previous month", "current month" and "ytd" -- Current
Month & YTD are working fine, but I am having trouble directing the
VLOOKUP to find "May 07" and then pull the value in column 2, -1 row.

This is my current formula:
=VLOOKUP(CONCATENATE($A17,C$16),'2007'!$B:$HL,(VLO OKUP($B$4,$BJ:$BK,
2,FALSE)),FALSE)

The part that I'm confused about, is the 2nd VLOOKUP in the formula.
The '2' returns the column numbers for the results I want, and this
works wonderfully for current & ytd numbers. How do I direct the '2'
to actually pull from column 2 & -1 row?

Thanks again for all of your help -- I'm a beginner & am trying to
teach myself! :)

Thanks,
Ryan

Find Column Number via Lookup
 

Hi Ryan

Depending upon whether you are wanting Current or Previous, could you
not use a formula in B4 which returned either May or April.
Or, if B4 is your dropdown selecting the month, use cell C4 to make that
April or May depending upon whether you have selected Previous? Then use
C4 in your formula rather than B4.

Better still, take the second Vlookup out of your formula altogether.
In C4
=IF(xxx="Previous",OFFSET(VLOOKUP($B$4,$BJ:$BK,2,F ALSE),-1,0),
VLOOKUP($B$4,$BJ:$BK,2,FALSE))
where xxx represents a cell determining Current or Previous

Then your main formula becomes
=VLOOKUP(CONCATENATE($A17,C$16),'2007'!$B:$HL,$C$4 ,FALSE)

--
Regards

Roger Govier


"Ryan" wrote in message
ups.com...
Hi All,

I appreciate your replies. I realize that the number of cells vs days
in the year + weekly & monthly totals are more than the allotted
amount in excel. We only do business certain months of the year, so
it's less than A:HL (thank goodness!). Also, it's column number 28
due to the range I selected; I have a some labels in the first few
columns of the spreadsheet which throws off my lookup if I select
them.

I figured out how to search for the text of the Month label (April 07,
May 07, etc).

My only outstanding question is:

I choose a Month from my drop-down list (Validation) & I have a
VLOOKUP looking up the month from the validation list & pulling the
value from the selected column in a VLOOKUP (formula below). However,
I want to show "previous month", "current month" and "ytd" -- Current
Month & YTD are working fine, but I am having trouble directing the
VLOOKUP to find "May 07" and then pull the value in column 2, -1 row.

This is my current formula:
=VLOOKUP(CONCATENATE($A17,C$16),'2007'!$B:$HL,(VLO OKUP($B$4,$BJ:$BK,
2,FALSE)),FALSE)

The part that I'm confused about, is the 2nd VLOOKUP in the formula.
The '2' returns the column numbers for the results I want, and this
works wonderfully for current & ytd numbers. How do I direct the '2'
to actually pull from column 2 & -1 row?

Thanks again for all of your help -- I'm a beginner & am trying to
teach myself! :)

Thanks,
Ryan