Vlookup challenge

[trevms]

Hi everyone,
I have a workbook which has monthly worksheets. There is a summary
sheet which summarizes the worksheet for the current month using
Vlookup.
I have to create a new monthly sheet for every new month and have to
manually change the summary sheet by changing the name of the month in
the Vlookup Table array.
I'm trying to automate this process by using a Combo-box with the
names of the months in the workbook in the input range but I cannot
seem to get Vlookup to read the month correctly and errors appear.

Any ideas.
Thanks

TS

 

[trevms]

Thanks Stephen,
The problem still persists however.
If I place the DAta validation in Cell G27, the VLOOKUP picks up "G27"
instead of the data (July 2003) in Cell G27. How do I set the VLOOKUP
to read the text in the cell which is the name of a worksheet.

 

[trevms]

Steve,
Let me try one more time - its hard to explain.

I have worksheets named Jan, Feb, Mar etc. and then a final worksheet
named Summary.
I use VLOOKUP in the Summary worksheet which references to the current
month worksheet.
As every month starts, I copy the previous month and rename the
worksheet to the current month.
I then have to change each cell in the Summary sheet to VLOOKUP the
new month's name. I am looking for a quicker way to change the
Referenced month (or worksheet) in the Summary sheet and am trying
with Data Validation, Combo-box and INDIRECT functions to allow me to
just click on a month in one cell which would change all the VLOOKUP
References in the Summary Sheet.

Thanks for your help.

TS

 

[Stephen Dunn]

The approach sounds reasonable (except for copying previous month's sheet -
set up a template, and you won't have to delete data). What is the VLOOKUP
formula that you are using?

 

[Stephen Dunn]

If "July 2003" was in the cell A1 (chosen from the Data Validation list),
you would use:

=ROUND(VLOOKUP(A5,INDIRECT("'"&A1&"'!$C$65:$M$119"),11,FALSE),2)