vlookups: Having variable data in the range

[mrooks]

Dear Sirs

I am struggling to dea l with a current vlookup issue. The problem exists in this formula.

=VLOOKUP("Total Revenue",(E28),4,FALSE)

The value in cell E28 is as follows:

'[Consolidation 0509.xls]summary report'!$B:$E

Within the cell E28 done through a CONCATENATE I set it up to have variable monthly/yearly information to change in order for the spreadsheet to capture the new month's information. I am obviously seeking the Total Revenue figure calculation from the monthly consolidation spreadsheet to be put into my report. Automating the process would speed things up considerably.

Does anyone know whether this is possible or is there a better formula/VBA to be done to achieve this goal of basically achieving this formula

=VLOOKUP("Total Revenue",'[Consolidation 0509.xls]summary report'!$B:$E
,4,FALSE)

The values underlined are the ones I would like to change on a monthly basis using some type of formula in order to achieve my goal.

Thanks for your help. I have the Excel 2007 In Depth book in front of me however, can not find the answer.

 

[DonkeyOte]

You would need to use INDIRECT

=VLOOKUP("Total Revenue",INDIRECT(E28),4,FALSE)

HOWEVER note that INDIRECT only works with Open targets, if the target file is not open INDIRECT will return an Error.... if this is a likely scenario you could look into installing morefunc.xll and making use of INDIRECT.EXT function.

 

[mrooks]

Thanks for your response. Where could I download these functions?

 

[DonkeyOte]

Info: http://xcell05.free.fr/morefunc/english/

Download: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html