Spreadsheet reference help 1

[waladd]

I've been working on a work schedule for which one spreadsheet references another. Is there anyway to get the spreadsheet/cell info to auto-update. I have to do the schedule in a two week format. The formula I'm using (Thanks again Skip) is as follows:
=IF(WhatInteriorColor('[ANCILLARY 2007.xls]0225-0310'!$E$6)="Red","SICK",IF(WhatInteriorColor('[ANCILLARY 2007.xls]0225-0310'!$E$6)="Rose","L",IF(WhatInteriorColor('[ANCILLARY 2007.xls]0225-0310'!$E$6)="Light Yellow","ON",IF(WhatInteriorColor('[ANCILLARY 2007.xls]0225-0310'!$E$6)="Light Green","PH","OFF"))))

Where I need the 0225-0310 to be able to update, or be able to have it changed in the entire sheet, as every two weeks this value changes. I'm hoping the answer isn't to do it manually, please help. Also when I email it to my work address it shows the location of the file from my home computer and when I update it I get the #### value.

 

[SkipVought]

Hi,

"...every two weeks this value changes."

What is the value and in what manner does it change?

Are you storing each two-week chunk of data on a separate sheet?

If so, that is a BAD practice in terms of data storage. Your source data ought to be consolidated into a single source, from which, the the plethora of Excel's data analysis and data reporting features, you can REPORT in whatever date grouping you want.

Given a well designed table, you could generate a PivotTable report in a mere SECONDS, reporting in 2-week groupings, or for any fortnight span.

Skip,

_[red][/red]

 

[waladd]

I have one file for the year, I have a new tab for every two week period. This is how HR set up their Book. Hence all the colors, they have a key to tell them where someone is working or if they're sick. I've got seventeen people that I do the schedule for. I know it probably seems a little dumb to go through all this trouble just to keep the department schedule looking the same, but people in my department have problems with change.

 

[SkipVought]

1, you can only view one sheet at a time.

2, mutiple identically formatted reports, multiply maintenance if and when changes occur etc.

These reasons are why you ought to explore using reports with controls. You make and maintain ONE report of each kind, and use controls to FOCUS the report data to the user's needs.

Most of my application have one report sheet, usually a pivot table and one chart, that can be controlled to display one of DOZENS of different areas. This can be accomplished with a minimum of code.

You still did not answer the initial questions I asked before pontificating.

Skip,

_[red][/red]

 

[waladd]

the value is the name of the sheet tab, 0225-0310, 0311-0325, etc for each two week period. [ANCILLARY 2007.xls]0225-0310, the 0225-0310 is the tab for the next 2 weeks. The main problem I have is, I have to work with the way the HR person has set up her sheet, I cannot add any macros or formulae to it. I have mine set up to tell me how many ppl I have in each area, how many hours each person has and the number of total hours. She needs to be able just have times (6a-2p) and color coding that she can copy and paste without having any issues.

 

[SkipVought]

[tt]
=RIGHT(CELL("filename",B2),LEN(CELL("filename",B2))-FIND("]",CELL("filename",B2)))
[/tt]
the cell reference is immaterial,

Skip,

_[red][/red]

 

[waladd]

if you don't mind, how does it work. I pasted it into a blank cell and it showed me the name of that spreadsheet (tab). I take it I replace the file name (big leap there), after that I'm a little foggy. Should this be entered in the place of [ANCILLARY 2007.xls]0225-0310 in the formulae, or is this for those formulae to refer to to tell it where to look?

 

[SkipVought]

You said you were missing a value, "...the value is the name of the sheet tab..."

Well, there's the value.

Did you look at HELP on the CELL function? It will describe what CELL returns with what arguments. The only other part to understand are the string functions, RIGHT, LEN and FIND. If you patiently take it apart, you'll understand how it works.


"Should this be entered in the place of [ANCILLARY 2007.xls]0225-0310 in the formulae..."

No, CELL with the "filename" argument, returns the full file name path to the SHEET that the function is on.[ANCILLARY 2007.xls]0225-0310 is on another sheet. That's why I do not like to have these multiplicity of sheets. It makes formula maintenance (maintenance in general) difficult. There are much better ways.

Skip,

_[red][/red]