Getting an error message when i enter this formula - Not sure why

[Maune]

Good day all!

I am trying to enter a formula in a workbook that pulls it's data from other multiple workbooks. When I hit 'Enter' I am getting a pop-up box telling me the formula has an error.

This is the formula:

=SUMIF(INDIRECT(VLOOKUP($B$1,WBLINKS,2,FALSE))&"$J$3:$J$2500","=R",INDIRECT(VLOOKUP($B$1,WBLINKS,2,FALSE))&"$G$3:$G$2500")

where B1 is a dropdown box with a list of years.

WBLINKS is a named range that looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]S5[/TD]
[TD="align: center"]T5[/TD]
[/TR]
[TR]
[TD="align: center"]2015[/TD]
[TD]

[TABLE="width: 215"]
<tbody>[TR]
[TD][CBG DAILY COUNTS.xlsx]2015'[/TD]
[/TR]
</tbody>[/TABLE]


​

[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD][TABLE="width: 215"]
<tbody>[TR]
[TD="align: center"][CBG DAILY COUNTS.xlsx]2016'[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD][TABLE="width: 215"]
<tbody>[TR]
[TD="align: center"][CBG DAILY COUNTS.xlsx]2017'[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


In CBG's workbook the sheets are named 2015,2016,2017, etc.


Originally, this formula was set up for sheet 2015 and looked like this:

=SUMIF('[CBG DAILY COUNTS.xlsx]2015'!$J$3:$J$2500,"=R",'[CBG DAILY COUNTS.xlsx]2015'!$G$3:$G$2500)

It worked fine. Now, I want to allow the user to select any year to look at data and so I tried to modify the formula to look at different sheets in their workbook.

I have a feeling this is a simple fix, but I can't find an answer!


Thank you for any help

 

[Maune]

I just realized I left off the " ' " in front of the strings in column T. I do have them in my worksheet though!

 

[Maune]

is there anyone out there?

 

[Jonmo1]

You also left out the ! that goes between sheetname and range
And you misplaced the ) of the indirect.

try
=SUMIF(INDIRECT("'"&VLOOKUP($B$1,WBLINKS,2,FALSE)&"'!$J$3:$J$2500"),"=R",INDIRECT("'"&VLOOKUP($B$1,WBLINKS,2,FALSE)&"'!$G$3:$G$2500"))

 

[jasonb75]

Compare your new formula to your original one, there are some characters missing and a couple of missing parenthesis.

=SUMIF('[CBG DAILY COUNTS.xlsx]2015'!$J$3:$J$2500,"=R",'[CBG DAILY COUNTS.xlsx]2015'!$G$3:$G$2500)

So the revised formula should be

=SUMIF(INDIRECT("'"&VLOOKUP($B$1,WBLINKS,2,FALSE))&"!$J$3:$J$2500"),"=R",INDIRECT("'"&VLOOKUP($B$1,WBLINKS,2,FALSE))&"!$G$3:$G$2500"))

Is there any reason why you need to use vlookup? Based on your data layout you could simply use

=SUMIF(INDIRECT("'[CBG DAILY COUNTS.xlsx]"&$B$1&"'!$J$3:$J$2500"),"R",INDIRECT("'[CBG DAILY COUNTS.xlsx]"&$B$1&"'!$G$3:$G$2500"))

 

[Jonmo1]

Is there any reason why you need to use vlookup? Based on your data layout you could simply use

=SUMIF(INDIRECT("'[CBG DAILY COUNTS.xlsx]"&$B$1&"'!$J$3:$J$2500"),"R",INDIRECT("'[CBG DAILY COUNTS.xlsx]"&$B$1&"'!$G$3:$G$2500"))

+1
Good adjustment..

 

[Maune]

Thank you so much!

I knew it would be something simple like a parenthesis!

 

[Jonmo1]

You're welcome.

 

[Maune]

The reason I used Vlookup is because I'm not smart enough to figure another way to do it! LOL!

In the back of my mind, I kept telling myself, there must be a simpler way, but I couldn't see it.

This is why i love this board, thank you all so much for your help!