Inserting Sheetnames in function via Vlookup

[Shinner]

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

SUM("'SHEET1:SHEET2'!C3")

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:

=SUM(INDIRECT("'" & VLOOKUP("country1",A:C, 3,FALSE) & ":" & VLOOKUP("country5",A:C, 3,FALSE) & "'" & "!C1"))

Everything goes fine until it reaches

SUM("'SHEET1:SHEET2'!C3")

then it returns a #ref!

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

[timbo1957]

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]

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.

Function Sum3D(strWS1 As String, strWS2 As String, rngSum As Range)
    Application.Volatile
    Sum3D = Evaluate("SUM('" & strWS1 & ":" & strWS2 & "'!" & rngSum.Address & ")")
End Function

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

=Sum3D(VLOOKUP("country1",A:C,3,FALSE),VLOOKUP("country5",A:C,3,FALSE),C1)

[Shinner]

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]

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

Yes.

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.

[Shinner]

That worked, thank you!
Great solution to my problem, thanks a bunch.