I have a spreadsheet that I the first tab is just information from other tabs on the document. Is there a formula that will make cells on one tab fill automaticly with the information from another cell on another tab. The problem I am having is that the names on the other tabs change every time we update the information.
I attached the spreadsheet so you can see what I am trying to do. The first tab is the date and the (blank tabs) all get job numbers. My clerks are having to type the information in twice, once on the job numbered tab and then put the same information on the first tab. I have been searching for a formula but I cant find anything that will change as the tabs are named something diferent each time.
I hope I explained this so someone can understand what I am trying to do if not please let me know.


Thank you
Ronnie

Is there a way to maybe number the other tabs maybe in brackets or something and then a formula with a wild card/character and that number so it will know which tab to reference by the number when the name is changed on the tab but keep the tab number the same each time.

This sounds like a natural for use of the Worksheet_SelectionChange event, unless for some reason, you can't use an .xlsm file.

You do realize you didn't attach the spreadsheet to your original post, right?

No I thought I attached it. I see it is too big to attach. Sorry

No doubt you are now goggling "Worksheet_SelectionChange event".

With that you can use a macro and refer to the sheets by their "codename", then it doesn't matter what the tab name gets changed to, you would always be dealing with the same sheet.

I put in a sample of what I need for this to do. Not everything is filled this is just to show what I need it to do. I have the freedom to make any changes I need to this file also. I am just a novice at this though and know nothing about macros. If I have a sample to follow I think I can make it work though. If there is a way to do it without a macro that would be great also.

Attached Files

6-2-15.xlsx (210.5 KB, 11 views)

Couple of questions regarding operation of this workbook:

1.) What happens (or should happen) when a job number gets changed on a job sheet?
Asking this with respect to clearing of cells on the job sheet and/or summary sheet, and/or preserving summary of previous job number by adding the new job number to the summary sheet.

2.) Of the cells on the job sheet needing to be written to the summary sheet, which ones contain formulas?
I suspect at least the cells with the dollar amounts would be.

1.)We create two new sheets a week saving them as the date we open them so starting new everytime.
2.)I dont think that any of them have formulas that need to be written. They type the dollare amt in on the Tab page.

I think I will probably have to change the form on the Tabs page some also to be able to have a cell that has the information for a couple of the ones on the summary page also. If I have something to just get me started in the right direction I think I can just make the changes to make it work.

I'm not sure I follow the 1st answer. The "starting new everytime" leads me to think you mean new Workbooks rather than worksheets.

At any rate the attachment is just an idea. Hopefully other members will chime in on this.

None of the tab names matter but, and it's a big but, this relies on the summary sheet being identified by the codename Excel gives it. If your 1st answer is referring to the summary sheet being replaced we'll need to deal with that in another manner.

Give this a look.
I'd leave the BLANK sheet as a template, do the "move or copy..." thing to make copies to try, it has the necessary code with it, right click the tab and "View Code" to see.

Hope this helps or gives some insight towards the solution you're after. Return with any questions.

Good Luck
NoSparks

Attached Files

Ronnie_v01.xlsm (67.3 KB, 7 views)

Yes I ment workbook not sheet. It is totally new each time. This looks like it will work, I will have to study it and see how what you did works so that I can make the rest of the cells on the summery sheet fill in. I am sure I will have questions but this gives me a direction to work towards. Thanks
Ronnie

I'm sure you'll have questions, 'cause I anticipated cells with formulas and didn't include any macro to deal with them.

I don't think I did a very good job of asking my second question a couple of posts back.
I know cell C8 on the job sheets contains a formula to calculate a percentage and was trying to ask
if any of the other cells on the job sheet that need their value transferred to the summary sheet have formulas as well.

Concern here is that cells with formulas will not fire the Worksheet_Change event, which is what's being used, and will need to be dealt with in another manner.
Currently looking into this.

OK I made some changes to the spreadsheet so that there are cells to copy for each of the ones on the summery sheet. What would work is to just have it with 50 blank tabs that we could name with jobs. We have never had more than 50 so that should be enough. I also made a word document that has the cells that need to be copied form the Tabs to the summery sheet. I made a list for the first two lines.
Let me know if this helps in understand what I need to do.
Thanks for helping me with this.
Ronnie

Attached Files

	Spreadsheet Tabs.docx (13.5 KB, 7 views)
	5-12-15 Sample.xlsx (134.8 KB, 8 views)

Hi Ronnie

There's more than likely better ways to do this but I think this will work for you. Try it out and see.

I've hidden the Blank sheet, it acts as a template, so you don't want it to get renamed and used. Alt + F8 will give you access to a macro which will add 10 blank sheets at a time. This macro can be run at any time and will always add another 10 blanks.

Let me know how it goes.

Attached Files

5-12-15 Sample_2.xlsm (148.2 KB, 9 views)

This looks great. I will try it and let you know if there are any bugs in it. I really appreciate you working on this.

OK
The only thing I see that needs to change is on the summery sheet cell R5 needs to copy C26 instead of C25. That was my error in what I posted it. Sorry.
On the Tabs on Cell B39 I forgot to delete the information in quick quote that gets pasted in new on every tab. If you could delete that so that it does not show up on all the tabs.
If you could type in "Location:" on cell K27 on the tabs that would be great also.
I am not sure what all I can do without messing up the macro or I would do it.
Is there a way to make the summery sheet stay in the order of the tabs? Example: I move a tab from the end to the middle or vice versa it changes on the summery sheet to keep them in order as they appear at the bottom of the spread sheet? This is not a big deal but it would keep the summery sheet in order of the areas or job type as we arrange the tabs.
Again Thanks for all the help with this.