3D Sumif for non existent sheets

[learningbasics]

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

[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.

Discussion here https://www.excelforum.com/excel-new...ta-layout.html

[learningbasics]

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!

[tim201110]

1 check if sheet exists
2 make new list

i'd not recommend link the whole column

[learningbasics]

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.

[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.

[learningbasics]

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!

[MrShorty]

who wants each budget captured on a separate sheet for reporting purposes

I realize that there can be a learning curve when you don't know pivots/databases. The power and utility in using a good database with pivot tables is you can generate whatever report(s) the client wants from one or more pivot tables based on a good, flat data file. The common example is getting good montly reports, then someone asks to see the report by quarter. A few mouse clicks in a pivot table, and the pivot table is now reporting by quarter. When you have a bunch of separate montly databases, combining those into quarterly (or yearly or whatever time division you want) is usually more difficult and prone to error than a pivot table.

If you did not follow this link in the thread I linked to above, read this to see how this works: http://excelpivots.com/excel/pivot-table-source-data/

When all is said and done, don't let me tell you how to do your project. Remember that I am not a database guru, either, but I have seen enough questions here where, if the OP had taken a little time to learn basic database and pivot table skills, they would have been well served in the long run for generating many different reports.

[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.

[learningbasics]

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!

[Richard Buttrey]

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!

The intention was that you would merely list all your sheet names across row 3 of the Summary Sheet. Then copy and paste the existing formula to fill up the whole matrix.

I hear what you say about the separate sheets but rather than expecting a manager to find a particular sheet amongst a lot of tab names, a smarter way of operating would be to have a template that fills up from a master database when the project manager selects a particular job from a drop down cell.

[6StringJazzer]

You have cross-posted to https://www.mrexcel.com/forum/excel-...nt-sheets.html I have added this link because you are a new user.

Your post does not comply with Rule 8 of our Forum Rules: Do not cross-post your question on multiple forums without including links here to the other threads on other forums. Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. When you cross-post, you must provide a link to the cross-post.

Read this to understand why we ask you to do this.

[learningbasics]

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

[learningbasics]

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!