Auto popluate area name from unknown number of sheets

**Cidona** · 01-14-2011, 07:09 AM

Dear forum.
Wondering if someone could posibily help me out.
I found a downloadable workbook from J&R Excel Solutions (Thank you J&R if you're out there

). It takes care of the first solution I was looking for which was to be able to create new worksheets from a template (hidden within the workbook). Hoping now that there is an easy solution for the second part.
Which is...I am antisapating modifying the received workbook to suit my application, basically just using the macro for creating the new worksheets, and the concept of the eg '=SUM(Top:Bottom!B2) type formula where useful.
However in addition to the 'collecting' of numbers in the summary sheet, as this particular workbook's 'Summary' worksheet is set up to do, I would like to be able to create a type of list on the summary sheet automating listing certain details on all the worksheets.
For example, please see attached workbook. Here I have added 'Area Name' in the hidden template in cell A15. Then on the Summary sheet I have added 'List of Areas' in cell D18.
I would like to be able to have formula (or some sort of solution) for cells D19+ that would list all the areas. Since the number of areas is unknown, I wouldn't know how to set up a 'dynamic' type formula to accomplish this. The simplier the solution the better as I am operating with minimal toolset when it comes to excel and hoping to get a solution that I would be able to manipulate to achieve my needs.
Any advise would be great. Thank you.

**JBeaucaire** · 01-15-2011, 02:11 PM

What is an area? I would expect your sample workbook to actually demonstrate what you're asking. Sample data in the sheets, sample list manually created in D19+, etc. Help us to see what you're talking about.

**Cidona** · 01-15-2011, 08:30 PM

Hi JBeaucaire,
Thank you very much for the reply.
Please find attached revised workbook which will give a better idea of what I am looking to acomplish.
In it current form it is somewhat simplistic with only a few 'areas' (as in areas of a building), However my final workbook would have much more catogories, areas, etc.
So, the idea is, a salesperson would open this up (empty). Fill out the project name, address, date of bid and salesperson on the summary sheet. Then click the 'Add New Sheet' button on the Summary sheet. the user is given the option of imediatly after top or before bottom. Whichevers is fine. This then brings the user to the new sheet. I have the template such that it is reading the project info (name, address, salesperson and date) from the summary sheet. Then also in the new sheet (based on the hidden template), are sections for the salesperson to fill out. Namely 'Area Name' (such as basement, 1st floor, 2nd floor... as shown in my example workbook), and then the relevany costs for the area (based on set catogories, in my example workbook are set up as 'Demo work, Rough framing, Drywall, ...). The sum of the catogory costs for the area is added up in B19 of each area.
So, what I'm looking to accomplish is when the salesperson goes back to the summary sheet that each of the area names (pulling the info from cell B9 on the various sheets) would automatically be listed in column D with the corresponding total cost for the area (pulling info from cell B19 on the various sheets).
I then have the total sum for column E in cell B10 of the summary sheet.
I have for the purpose of explaining my desire entered the info I would want automatically pulled into columns D+E, but the hope is that this would be automated.
Thank you again for your reply. Please let me know if you require further clarification of the purpose or such.

**NickyC** · 01-17-2011, 06:11 AM

Hi
is this what you're after?

**Cidona** · 01-17-2011, 02:32 PM

Afriad not NickyC. Or at least from what I'm seeing in your workbook (sure you posted to the right thread?

). Thanks for the reply though.

**JBeaucaire** · 01-17-2011, 07:42 PM

I would change your existing macro to this to:
1) Put the sheets in the correct order as you add them
2) Add the List of Areas

At the beginning of a new project you need only have D18 with the List of Areas title, then use the ADD NEW SHEET button to add your first sheet, the macro will add a sheet and put in the first entry in the areas, then continue.

Please Login or Register  to view this content.

**NickyC** · 01-17-2011, 08:57 PM

apologies, cidona - I posted this in the wrong thread!
hopefully JBeaucaire has answered your query

**Cidona** · 01-21-2011, 07:03 AM

No worries NickyC, at least I wasn't missing something obvious. I was feeling a bit dumbfounded at first

JBeaucaire, you are my new favourite superhero, just wondering what color your cape is

Thanks so much!

Auto popluate area name from unknown number of sheets

LinkBack

Thread Tools

Rate This Thread

Display

Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Re: Auto popluate area name from unknown number of sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions