VLOOKUP difficulties

[trevms]

Hi everyone,

 

[Dan E]

Have you tried using data validation as opposed to a combobox?
It just seems like it would be that much easier since your list can be
referenced just like a cell!

Dan E

 

[trevms]

I have DAn,
The same problem seems to persist.
When I reference the VLOOKUP to the cell which has data validation, it
returns the Cell Reference not what is entered in the cell or selected
from data validation.
For example all the months are listed in the cell G27 by data
validation. WHen I reference the VLOOKUP cell it returns G27 and not
July or August.
Any suggestions?

 

[Dan E]

So, say your data validations is in cell A1, your lookup range is B1:C10 (ie
column B contains Dates and C contains the values your looking up), your
formula would be something like
=VLOOKUP(A1, B1:C10, 2, FALSE)
and when you punch the formula in it retuns A1???

Dan E

 

[trevms]

Dan,

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.

I understand how VLOOKUP works I just do not know how to include a nested INDIRECT function looking uo a range of data. Thanks for your help.

TS

 

[Dan E]

=VLOOKUP(B8,INDIRECT(A8 & "!E1:F20"),2,FALSE)
B8 is the value to lookup
A8 contains the sheet name
E1:F20 is the lookup range
2 is the column

Dan E

Dan,

nested INDIRECT function looking uo a range of data.

 

[Dan E]

The dropdown has to have the EXACT sheet name!
if the dropdown had "July" and you wanted sheet "July 2003" the indirect
part of your formula would be:
INDIRECT(G27 &" 2003!C65:M119")
I'm not completely sure that's your problem, but it's all I can think of?

Dan E

 

[Steve Smallman]

have you tried including the single quotes within double quotes to build up
the full string that comprises the sheet reference?

Steve

 

[trevms]

Dan, I had someone else reply to me and I think the strings after
INDIRECT were the problem. This is the formula now and it works.

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

Thanks for your help.

TS