Inserting Sheetnames in function via Vlookup

**Shinner** · 03-17-2011, 06:12 AM

Hi,

I'm having a bit of difficulty solving a problem.
What I want my formula to return is the sum of a cell in a range of sheets. The name of the sheets are found via a Vlookup function. Problem I'm getting is that I can't get it to work properly. I can get my sheetnames from the Vlookup fine by use of 2 vlookup's in the formula but I think the problem is that whatever I do I always end up with

Please Login or Register  to view this content.

and the quotation marks become the problem and the cell returns a #ref! error. (I think that's the problem)

The full formula I'm trying goes something like this:

Please Login or Register  to view this content.

Everything goes fine until it reaches

Please Login or Register  to view this content.

then it returns a #ref!

Any help or remarks on what I'm doing wrong?

**timbo1957** · 03-17-2011, 06:54 AM

If you were putting the result of the sum into A1 then this would work -

Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(Sheet1!R[2]C[2])+Sheet2!R[2]C[2]"

**DonkeyOte** · 03-17-2011, 07:07 AM

Shinner, I'm afraid you can't build 3D references in the manner.

For an INDIRECT construct you would need to detail all sheets individually within the start:end sheet references rather than just the start & end sheet references themselves.

You might want to consider use of VBA - ie a Volatile UDF.

Please Login or Register  to view this content.

the above stored in a standard module in VBE would be called from a cell along the lines of:

Please Login or Register  to view this content.

**Shinner** · 03-17-2011, 08:42 AM

Hey Donkey,
I like your solution all though i haven't gotten it to work yet.
Are you sure the above function is correct and would work? I put it in the General code window for the workbook in the Visual Basic part of Excel 2007 and saved the document.
Then I tried to call the function with the formula you provided but I keep getting a #name? error, like it doesn't recognize the new function.
I'll admit I'm pretty ignorant about the VB part of excel, anything I'm doing wrong?

**DonkeyOte** · 03-17-2011, 09:06 AM

Originally Posted by Shinner

Are you sure the above function is correct and would work?

Yes.

Originally Posted by shinner

I tried to call the function with the formula you provided but I keep getting a #name? error, like it doesn't recognize the new function.

The #NAME? error you describe results from the UDF being "unavailable"/"non-existent"

To implement the UDF correctly:

Activate VBE (Alt + F11 from native XL)

Clear whatever you added previously regards this function

Via the Insert menu select Module

Paste the code provided into the resulting window

Close VBE returning to native XL

You should then find you can call the UDF without any issue

In XL2007+ - when saving the file ensure it is saved in a macro enabled format (eg .xlsm)

Ensure also your macro security settings are such that when opening the file you are prompted to enable macros.