Change number of rows to reflect source data

[BLRITCHIE]

Hi all,
I would appreciate a bit of help on this as I am struggling to work out an easy way of solving my issue.
The attached workbook shows a cut down version of the real thing, with 2 sheets of data and a summary sheet. The full book has many more sheets, not all of which are used to generate the summary.
I need to be able to link the supplier and associated value to the name of the sheet, hence the set up used, as this allows the user to change the sheet name which is updated in the summary (this data then gets sorted in a pivot table). If there is a better way of doing this, I am happy to learn.
However, my problem is when rows are inserted into the main sheets, the summary doesn't capture these.
I need some code that will check the sheets (but only the ones needed - I can generate a list of these) and return the supplier name and the corresponding total value into the summary sheet, taking in to account all the rows that have data in them.

I hope that all makes sense.
Many thanks,

[foxguy]

Hi BLRITCHIE;

What you're asking for can be done.

I would use macros to do it. If you want to go this way, I would want to know if you have worked with macros before? Also do you know how to set up range names? If you haven't I would write it differently.

I think it's possible to do it with formulas, but I'm pretty sure it would be harder to set up than macros, and they would be very complicated formulas. I'm not even sure that I could do it, but someone better than me might be able to.

[BLRITCHIE]

Hi,
Thanks for the reply - I have actually been using some of your work from a previous thread (http://www.excelforum.com/excel-prog...worksheet.html) that I came across while trying to work this out myself!
I have a little experience with macros (mainly chopping up other peoples code) and range names, so am relatively comfortable with this approach.
As I said, I have been trying to modify the work you did on the other thread to suit, and have had some success, but am still struggling when rows are inserted into source sheets, as the formulas used dont copy into the new rows. I have written a macro that will copy the formulas in when it is run, and that seems to work, but its all a bit messy. The other big thing that is causing headaches is the fact that the sheet names are user defined, so when they are changed, I need to reflect that.

Any help would be much appreciated.

Cheers,

[foxguy]

Hi BLRITCHIE;

I'm working on your problem. The first thing that will help is to name the cell in each worksheet that has the sheet name in it (F9 in the file I'm looking at). The names need to be attached to the sheet, not the workbook. Just in case you don't know how, here are instructions.

1) Put your cursor on the cell with the sheet name in it.
2) In menus INSERT>NAME>DEFINE...
3) Type the name with the current sheet name in it with ['] around the sheet name. 'Feature 1'!SheetName (the ['] let's you have spaces in the sheet name).
4) The Refers To: should say ='Feature 1'!$F$9
5) When you're click "Add" you should see "Fearture 1" on the right side of the list of names. That indicates that the name is attached to that sheet. When there is nothing on the right side of the list, that indicates that the name is attached to the workbook.

Do this for every sheet (except Summary)

Now your formula on Summary cell B2 can be changed to ='Feature 1'!SheetName instead of ='Feature 1'!F$9. This will allow a macro to fill in the formula for you. With the current formula (='Feature 1'!F$9) the macro would have to figure out where the sheet name is on the sheet. By naming the cell with the sheet name in it, the macro doesn't have to look for it. There are other ways for the macro to determine the sheet name, but this is the easiest.

[foxguy]

Forgot to mention, by naming the cells with sheet names in them, the macro can build a list of sheet names for a Listbox, so that you can easily select which sheets to include on Summary.

[BLRITCHIE]

Great, thanks for this.
I am still trying to do it using formulas, but its getting more and more messy and unstable, so I think VBA is the way to go.
Looking forward to seeing what you can come up with,
Cheers,

[foxguy]

Hi BLRITCHIE;

The attached file is just 1st draft. I want to make sure that this kind of format will work for you.
Open the file and select and unselect sheets in the ListBox. They don't do anything yet. The plan is to have the report to the left change as you select or unselect sheets. If that works for you I'll continue, if not then give me an idea of how you want it to work.

[BLRITCHIE]

That's perfect - I hadn't even considered having the sheets included in the summary as optional - adds a whole new dimension to the usability of the sheet.
Thanks so much for you work - really appreciate your time.

[foxguy]

Hi BLRITCHIE;

In message # 1 you said:

I need some code that will check the sheets (but only the ones needed - I can generate a list of these)

I interpreted that to mean that you wanted to be able to select what sheets were included in the summary. So if what I set up is not what you wanted, you will have to explain again.

Of course, if what I'm setting up is better for you then that's fine.

[BLRITCHIE]

Ah, I see what you mean. Originally I had hoped to limit the sheets that were included to a defined list, set by me in the template, so if other users added sheets, they would not be included in the summary (sheets from other projects are often added to the book for easy reference, but the figures are not to be included). Your suggestion, having the sheets selectable, is a much better work around as now users can add sheets at will, and where needed easily add them to the summary.
Sorry for the confusion, and hopefully its a bit clearer now. At the very least, I am learning how to phrase any future questions I have in such a way as to be unambiguous.
Cheers,

[foxguy]

Your suggestion, having the sheets selectable, is a much better work around as now users can add sheets at will, and where needed easily add them to the summary.

So there will be sheets in the workbook that are not to be included in the Summary. I'm going to set up the macro to put into the sheet list only those sheets that have a cell named "SheetName". And I will leave it to you to name that cell in the sheets you want included.
Does that work for you?

[BLRITCHIE]

Perfect, thanks, that will work really well for me.
Cheers,

[BLRITCHIE]

Hi Foxguy,
I hate to push when you are doing me a favour by helping, but I was wondering if you had had the chance to look at the problem I have. I have been having no luck myself, mainly due to complete ineptitude at programming!
Thanks very much,

[foxguy]

Hi BLRITCHIE;

Sorry, somehow your thread got put into a folder where I was waiting for you to respond. It was supposed to be in a folder that I'm constantly checking to see if it's done.

I'll work on it right now.

[BLRITCHIE]

Thanks, I really appreciate your help!

[foxguy]

Hi BLRITCHIE;

Here's first draft. It turns out to be a lot more complicated than I thought. I feel confident saying that if you want to change it later on, you will probably have to get me to update it. And I can't guarantee that I will always be around. If the economy improves and I get back to work, I may not have time to keep checking the forum. I don't expect that to happen for at least a year, but I don't want you to say I didn't warn you.

On every sheet that you want included in the list of sheets on Summary, you need to define a cell "SheetName" and it needs to be 1 row below the last row of data. You can see what I mean on Feature 3 where I put in a formula for the last row of data. The last row on Feature 3 is 10 and 'Feature 3'!SheetName is on row 11. You can add or delete as many rows as you want above "SheetName". Currently, if you add or remove rows on one of the Feature sheets, when you go to Summary, you will need to unSelect that sheet and reSelect it to get it to update. You can put 'Feature 3'!SheetName in any column as long as it is one row below the last data row.

On Summary, the Sheet Names column can be hidden. If you want to move it, you need to MOVE THE ENTIRE COLUMN. I haven't figured out how to only move the cells and have it work. But I'm working on it. You can move the "Select Sheets To Include" ListBox to anywhere on the Summary sheet.

[BLRITCHIE]

Hi Foxguy,
I have managed to work out a way of doing this that suits me - not very elegant but it works fine!
Thanks for your efforts.

Edit - I havent bothered to post my code as its all pretty specific - let me know if you want to have a look.

Edit 2 - I am such an idiot!!!!! I have been checking the thread and never noticed that there was a second page, so didnt realise you had posted something until I just wrote this last message.
I will have a look at what you have done and work it into my code, as its a much better way of handling the problem.
Thanks again, and I will let you know how I get on.