Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to specific cell in named range in Excel formula | Excel Worksheet Functions | |||
How do I to refer to a (named) cell in Excel header/footer? | Excel Worksheet Functions | |||
In VBA, How to Refer to Cell In Specific Column But Selected Rows? | Excel Discussion (Misc queries) | |||
???Refer to a specifc cell in a named range | Excel Worksheet Functions | |||
Using 'If' refer to specific words in a cell containing text | Excel Worksheet Functions |