Refer a specific cell in a named cell range
 

Hi,

I am creating a sheet with information on the availability of computer
systems i manage.

I created a sheet with system name and 7 columns for days of the week
(Mon-Sun).
For every system I enter the amountof hours the system is available on
everyday of the week. So for eg "systemA" on Sunday will be "12" hours
and on Monday wll be "24" (max) hours and so on.

In a second sheet, where i capture known unavailabiity I have system
name and columns for dates for 3 months. For every system I record for
everyday how many hours it was "actually" available. So the first sheet
is "expected" and the second sheet is "actual" availability.

The problem:
Now I want to display in a third sheet what is the percentage
availability of systems today. So for every system I want to do a
(actual/expected*100) for today's date.

Here is what I was planning to do now and need help with....

I would create a named cell range for every system from monday to
sunday in the first sheet. For ex: for systemA I would create
"systemAweek" with 7 cells of data from mon to sunday.

In my last sheet to show systemA's availability of today's date I would
put in the following formula:

=(24-'Sheet2'!J9)/'Sheet1'!systemAweek%

Here I would need to refer the specific cell in systemAweek that refers
to today's day (1-7) and I cant seem to figure how to make it work.

Any ideas??

Thanks a ton in advance!!

Refer a specific cell in a named cell range
 

You can use the OFFSET worksheet formula along with the WEEKDAY function:
=(24-'Sheet2'!J9)/OFFSET('Sheet1'!systemAweek,0,WEEKDAY(TODAY(),2)-1,1,1)
I think I have all the parameters OK based on your description, but if not
use Help or the formula editor to tweak it.
--
- K Dales


" wrote:

Hi,

I am creating a sheet with information on the availability of computer
systems i manage.

I created a sheet with system name and 7 columns for days of the week
(Mon-Sun).
For every system I enter the amountof hours the system is available on
everyday of the week. So for eg "systemA" on Sunday will be "12" hours
and on Monday wll be "24" (max) hours and so on.

In a second sheet, where i capture known unavailabiity I have system
name and columns for dates for 3 months. For every system I record for
everyday how many hours it was "actually" available. So the first sheet
is "expected" and the second sheet is "actual" availability.

The problem:
Now I want to display in a third sheet what is the percentage
availability of systems today. So for every system I want to do a
(actual/expected*100) for today's date.

Here is what I was planning to do now and need help with....

I would create a named cell range for every system from monday to
sunday in the first sheet. For ex: for systemA I would create
"systemAweek" with 7 cells of data from mon to sunday.

In my last sheet to show systemA's availability of today's date I would
put in the following formula:

=(24-'Sheet2'!J9)/'Sheet1'!systemAweek%

Here I would need to refer the specific cell in systemAweek that refers
to today's day (1-7) and I cant seem to figure how to make it work.

Any ideas??

Thanks a ton in advance!!

Refer a specific cell in a named cell range
 

Thanks a lot. This indeed worked. Yes I had to tweak the cell
references but that was the easy part :)

Thank again!

Manpreet A