Highline Excel Class 08: 3-D Cell References Add Across Sheets

Ok here I go. I know a enough to be dangerous, please bear with me and offer any available advise.

Current challange.

3 employees in group. Many 1/2-5 days jobs constantly coming in. Main sheet has columns for Priority, assigned employee initials, work type, status and job description.
Seperate sheets for each employee. I want to add all jobs to main sheet, assign priority and employee and have assigned jobs(row) copied to each employee sheet. Would like to update and add jobs to main sheet and have subsiquent sheets updated.

I've inherited a workbook with 100 or so tabs. There are absolute references to cells on the summary page scattered about all over the place. Hundreds of them, all over everywhere, with no easy way to find them.

Now they want to be able to add/delete rows on the summary page and sort.

I want to replace their absolutes with named ranges. Is there an easy way to find every cell that references the summary sheet?

Sorry if this is a dumb question. I have not used Excel since the 80's (when I knew everything about it), but obviously that was many versions back... I jumped into using VBA, and that's starting to make sense, but I'm still missing some of basics.

Using Excel 2003.

Thanks!
Jennifer

I was wondering if it was possible to autfill formulae pointing to another workbook over mutiple worksheets somehow. I know it sounds wierd but hear me out

I have 31 sheets (dates) in the main workbook

Each has a cell which points to a reference of a list in another book.

Eg

Book1.xls - SHEET 1 - CELL A1 = "='[Book2.xls]Office stats'!$G$1"
Book1.xls - SHEET 2 - CELL A1 = "='[Book2.xls]Office stats'!$G$2"
Book1.xls - SHEET 3 - CELL A1 = "='[Book2.xls]Office stats'!$G$3"
.... see the progression?

Problem is I have to link the formula for each worksheet everytime and I cannot figure out any other way to do this, it's driving me insane!!

ok, let me try to explain the problem in words. I am writing a VBA macro in excel. I have several sheets in this one workbook........one sheet for each day in a given month. Sheets for each day are labled as 3_1, 3_2 for march 1st and march 2nd respectively. I also have 4 sheets for the 4 weeks in a given month. The weekly sheets are labeled week1, week2 etc....

What i am trying to do is this:
In the week1 sheet i am trying to "put" a formula in say cell 9,12 which sums up the same cell in the first 7 days of the month. But i am having issues. Does anyone have a good way to do this via VBA?

David

I have a template which consists of a sheet of data and a sheet with line graphs referencing that data. When I copy both sheets to a new location (changing the names of the sheets at the same time), my charts refer back to my original data sheet. Is there an easy way (or a macro) where I can change all of the links from the old data sheet to the new data sheet.

Thanks in advance
ChemistB

I have a workbook that has a summary sheet that refers to a dozen other sheets that each have the names of cites.

I have taken one of those city sheets (London) and added a macro and some changes, and now I want to put that macro and those changes into all the other city sheets.

If I simply highlight the entire sheet and copy and paste the contents into the other city sheets, the macro buttons don't appear. (the macro is one that hides columns with an x in the first row, and shows them again if required. There are two buttons: "Hide" and "Unhide")

If I copy the sheet itself, I have to delete the original sheet before I can replace it with the new sheet by renaming it. ( I make a copy of "London" and it becomes "London (2)". If I want to rename it to "Paris", I have to delete the original "Paris" first) This makes all the references to "Paris" in the summary sheet change to "Ref". When I rename my "London (2)" to "Paris", the summary sheet does not revert back to "Paris" in all the references. It stays at "Ref". This means I have to go into each cell, and change Ref to Paris. For example the cell with =(#Ref!D266) has to be changed to =(Paris!D266)

I would have to do this to hundreds of cells for each city.

I have tried to save the summary sheet to another workbook, and then delete and change all the city sheets. Then delete the summary sheet full of "#Ref!s" and replace it with the summary sheet I saved to another sheet. When I do that, all the references refer to the temporary workbook I saved the summary sheet in, not the original.

So my questions a

Can I copy all the contents of my sheet called London to my sheet called Paris, including macros and buttons?

If not, how can I replace my sheet called "Paris" with an updated sheet called "Paris" without affecting the references in the summary sheet when I inevitably have to delete the original sheet called "Paris" first?

Thanks for any help on this issue

I have an excel template (at work) where we made some modification to some of the sheets.

The template behind has now been modified, and I want to copy some of the master sheets to my modified workbook.

The sheet I want to copy has references to named cells like NPV, etc.

However after copying the sheet, these references does not work. The cell says =NPV but it does not read the value?

Can anyone help?

I have an excel template (at work) where we made some modification to some of the sheets.

The template behind has now been modified, and I want to copy some of the master sheets to my modified workbook.

The sheet I want to copy has references to named cells like NPV, etc.

However after copying the sheet, these references does not work. The cell says =NPV but it does not read the value?

Can anyone help?

Hi all!
Sheet 1 is a summary sheet, its the Master.
Sheet 2, 3, n, n+1 is detailed slave sheets.
Sheet 1, the Master, has references to the same cells in the slave sheets, lets say the first row; A1-A10
The reference is a simple "=slave1!A1" "=slave1!A2" and so on.

My challenge is when I insert a new sheet, I want to automatically add a new row in the summary sheet with the same references, or formulas if you like.
So if I want to create 100 new sheets I don't have to make all the summary references manually.

I hope this was kinda understandable

I am working with files with defined range names. These name ranges refer to
on cell only. I want to change all formulae in the file: change range name
back to cell reference (so I can delete the range names).

E.g. cell $A$42 is called range "ip_con" - there is a formula in cell b99
that says "=ip_con +200". I want the formula to say "=$A$42+200".
Obviously, there are a lot of these references, so looking them by manually
is NOT an option.

Any help would be appreciated!!!!