Copying worksheets issue

My goal: To have a workbook that summarizes worksheets that are completed by vendors outside my organization. I'm trying to set this up for a large number of projects and have other users in my organization use this so I want to simplify the process.

My problem: When I copy the worksheets from the vendors into my workbook the duplicate name causes the worksheet to be appended with a number, e.g. (tabname 02). I tried deleting the blank worksheet and renaming the one copied in but I lose the reference link in my summary worksheet.

My workarounds: Use the "Find/Replace" feature to "repath" the worksheet name and then delete the blank worksheet but I find that inelegant and cumbersome. I have also tried to use the name function to assign a variable to the worksheet name but it doesn't seem to work. Does anyone have suggestions on how to accomplish this more easily. Or, is there a better way to create a summary sheet that reads a number of other files.

Thanks.

Tom

Hi Tom

My solution depends on the structure of the workbook received from the vendors being exactly the same.

I would set up my master workbook with lots of blank sheets, and, using a macro, move all the data from the vendor's sheet to my master file, renaming the sheet in my master file.

If you are interested in taking this further, let me have a copy of you master file and vendor file (suitably anonymised)

I will be interested in seeing other solutions

Regards
Alastair

Alastair-

Thanks for your help. I'm basically trying to figure out a way to allow someone to send out the "Fee Detail" Sheets to consultants as separate workbooks. Upon receiving them completed simply paste the sheet back in the "Fee Estimate" workbook so that the "Summary Sheet" will read the "Detail" sheets and provide an over all summary.

I thought I might have a cell that has the value defined as a Name so that I could use that Name in a function and the end user could match the Name to the proper sheet and it would update all the formulas. I just can't seem to get the syntax correct.

I'd appreciate any advice.

Thanks,

Tom

Hi Tom

Thanks for the update. Can you let me see what comes back from the consultants? Is it an exact copy of 9999E? - if so, does one consultant fill in part and another fill in another part?

Regards
Alastair

Alastair-

Attached is my complete assortment of files. The intention is to send the various Proposal.xlsx files to my consultants who fill them out and return them. I then drag and drop them into the Summary file, in this case 1234 - My House.xlsx. Then I use the Find&Replace feature to 'repath' the sheets designations in the formulas. It works, but seems easy to go wrong in the hands of someone who is not experienced with it. Not many people will be using this, but I don't want to be the support person if things go wrong. My thought was to provide a defined Name to each sheet so that the user can just retype the name in one place. It just seems that Excel doesn't allow defined Names with sheet designations. (Or I cannot figure out the syntax)

While putting this together for this post I noticed a curious thing. The currency format I used gets confused when I paste the new sheets in. It seems to pick one of my custom formats randomly. Fortunately closing and reopening the file fixes the bug. Not sure what is going on there.

I appreciate your advice but I'm trying to avoid using a macro, but if you have some advanced knowledge on how to use defined Names in the context of a formula or if maybe the INDIRECT formula is an appropriate use in this situation that would be great help.

Tom

Hi Tom

Thanks for the files - I think I understand what happens.

I presume that you are just trying to test me or do you really need 2 copies of LC and DC in House?

Nevertheless I think that the answer is as I first suggested. You have the House file set up with LC, DC etc included as blank structures, with all paths and references set up. When the consultants return their figures, just copy and paste everything unto the appropriate sheet in House. Very little skill involved, as you will be copying all of one sheet to another. No re-pathing, no name changes.

The foregoing seems a bit straightforward, so please let me know what I am missing.

Regards
Alastair

Alastair-

Yes, you described the process that I have in place now. I just copied LC and DC into the House.xlsx file to show you what I am doing. It works for me, but it has its drawbacks as if you mistype the new worksheet name the link will be broken. I'm trying to make it more robust as I will be sharing this excel file structure with other people in my organization.

Tom

Tom

I do not see the need to have any links.

You have the House file with several sheets one of which is called DC Fee Detail which contains all titles etc, but no figures. You have received from your consultant a file called DC Fee Proposal. Select all from DC Fee Proposal. go to the House file sheeet DC Fee Detail cell A1 and paste.

Hope this works for you.

Regards
Alastair