3D Sumif for non existent sheets

Hello!

First time poster and excel novice, so might require some more explanation!

I have an excel workbook that will eventually have multiple sheets, but at the moment only has two.

I have used a VBA code I found online (couldn't explain it!) to generate a list of worksheets.

On my summary sheet I have a list of names in Column B, and I would like to do a 3D sumif where in each worksheet column B is considered for that name, and the number is column I is returned. I can do this by using the formula;

=SUMPRODUCT((SUMIF((INDIRECT("'"&Worksheets&"'!$B:$B")),B4,(INDIRECT("'"&Worksheets&"'!$I:$I")))),0)

where B4 refers to the name I'm searching for in this particular case.

I intend on allowing up to 50 sheets in this workbook, therefore, I have allowed for 50 names in my range called 'Worksheets'. The problem is that the majority of the rows in the range are empty, and therefore the formula above returns a #REF error. The formula only works when all 50 sheets are generated and therefore there are no blank cells in the range.

Is there any (novice friendly ) way around this? is there a way of generating a dynamic range that grows as the list grows? or a way of getting the formula to exclude blank rows in the range?

Thanks in advance for the post, and sorry if this has been posted in the wrong place/posted before.

Thanks

1 check if sheet exists
2 make new list

i'd not recommend link the whole column

Originally Posted by MrShorty

Before pursuing this approach to the problem, I would suggest that you look carefully at the overall task and see if it would be better approached as a database + pivot table project. I am not an expert on databases and pivot tables, but I see a lot of questions like this on the forum where the overall project would be easier and more powerful if the data were all combined into a single database/sheet, then use pivot tables and other database summary functions to summarize or extract the desired data.

Potentially but I'm making the workbook for someone else, who wants each budget captured on a separate sheet for reporting purposes....and also I don't know much about databases!

Originally Posted by tim201110

1 check if sheet exists
2 make new list

i'd not recommend link the whole column

Thanks for the reply - my problem is a bit different to this. In this example it assumes that the VBA list is complete, and your second column returns which sheet actually exists which produces your third column. The problem I have is that my VBA list is essentially your second column, i.e. it only generates a list of existing sheets, and I don't know how many sheets there will be with how many names. So if you image in your example, the second column would grow as you add more sheets, so the third column, or your range, would have to be dynamic, or you have to extend the range to include up to 50 potential names. I've gone with the latter, because I don't know how to make a dynamic range, so I've selected 50 rows in your third column, and labelled that 'worksheets' so some of the rows are empty, hence the error sign.

Originally Posted by Richard Buttrey

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Thanks for the reply - the spreadsheet I have is complicated and has a lot of confidential information so would required heavy editing and recreating for me to upload, so I've created an example file of what I want to do, including the VBA code I'm using. I'm hoping that it's attached to this reply, but might have to post separately if I can't do it! If I could make it work on the attached then I can make it work on my larger one!

Originally Posted by Richard Buttrey

I don't think the #Ref is anything to do with blank Cells, rather the way yuo have used INDIRECT. Your formula is attempting to look up Sheets called Person 1, Person 2, etc. which of course don't exist.

Using this method I'd be inclined to build a table matrix as shown in the attached.
Try and avoid using whole column references, in some situations that slows things down enormously

Personally I'm never in favour of using different sheets for thinks like Products/Months/Departments/Periods etc. The first thing to concentrate on when creating a system is to ensure all your data is in a normalised two dimensional table of rows for indivdual records and column fields for the types of information you're recording.

Even if there is no way you can get the original data in a single table I'd still recommend a pre-processing macro that would create it for you from the individual sheets. With a proper normalised data table the world is your lobster (as they don't say) and you have a more elegant and simple way of analysing your data.

Thanks for the reply! The problem with the attached is that your table on the summary sheet is assuming I will only have the three sheets, if I was to add a budget 4 sheet I don't know how that would work.

Also thank you on your notes about databases, It's on my list to understand - but on this particular case there is a lot of information that needs to go on each sheet, and that need to be kept separate at the request of the project manager. I can keep them all separate with a lot of manual links and inputs, but I'm trying to avoid manual inputs because that increases the chances of the spreadsheet failing!

Apologies, I didn't realise! I was just keen to get a solution ASAP! won't happen again!

Hello,

OP here, Thank you everyone for your help - I've got a solution on the other forum I posted. I can't post the link as a new user, but it has been posted on this forum; it was a simple silly iferror missing! #

Thanks everyone! much appreciate the help!