On Jun 19, 1:56*am, GS wrote:
Frankly am really getting confused. Any help from the experts would be
highly appreciated

Suggestions...

I think you should stick to your original table layout as that will
definitely be easier to work with. For example, your headers:
* A * * *B * * * * C * * * *D * * * * *E * * * * F
* Item * ItemOut * ItemIn * Duration * Overlap * =CurrentOverlap

where each column (A to E) is a local defined name matching the field
name, and defined as column-absolute, row-relative. For example, A1 is
named "'Sheet1'!Item" and its RefersTo is "=$A1" while the active cell
is in Row1. B1 is named "'Sheet1'!ItemOut" and its RefersTo is "=$B1"
while the active cell is in Row1. And so on through "Overlap". (**don't
include the double quotes in Name or RefersTo)
//

Also, I assume that Item/ItemOut are empty unless an item is logged
out. Thus, logged out items will have ItemIn empty until they're
returned, and Duration will have the meter running to show how long the
item has been out. This can be done with a cell formula until you log
the item back in:

Make all defined names local by prefixing the name with the sheet name
wrapped in single quotes, and then the exclamation character, as
follows.
Name: "'Sheet1'!TimeOut"

RefersTo:
* =IF(AND(ItemIn<"",ItemOut<""),ItemIn-ItemOut,NOW()-ItemOut)

We will use this formula in the Duration column. Note that this formula
will only recalc when the sheet recalcs as a result of changes or
activation, and so you may need to occasionally use the F9 key to force
a recalc.
//

Also, I assume your definition of 'overlap' refers to 2 or more items
being out at the same time. If so then you contradict yourself later
when you state that if a third item goes out that the overlap hours
stay the same. I can understand why you say you're confused. That said,
for now I will proceed on the premise that we will consider TimeOut
overlaps as being the cummulative duration that 2 or more items are
currently logged out. You can change this however you decide it should
be later, but this will allow us to build a solution that we can start
working with in the meantime.

Cell $F$1 (named "TotalOverlap") will contain a formula that sums all
TimeOut overlaps as the cummulative overlaps value. This will only
display a value if 2 or more items are out at the same time.
//

So now it remains to design the rest of your spreadsheet so this will
work. I propose that when items are logged out you use a keyboard
shortcut to the date&time into ItemOut, and enter the formulas we'll
use in Duration and Overlap. When the item is logged back in you use
another keyboard shortcut to hardcopy the Duration formula's resulting
value, and set ItemIn date&time. We will do this via code in a standard
module in your workbook. This will hardcopy the formula results so they
are stored as constant values, providing you historic data as to when
each item went out and was returned. It will also persist the Overlap
formula in case subsequent items are logged out while there's any
existing items still out.

I suggest using a formula for Overlap that uses an enhaced version of
the same one we use for Duration, to also monitors things while 2 or
more items are still out. So while ItemIn is empty, Duration is keeping
time as suggested with the above formula, and Overlap is keeping time
while there's more rows with ItemIn empty. This will require defining
the ItemIn column as a named range so we can get a count of the empty
cells. This should be a dynamic range so it adjusts to include only the
number of rows in the Item column as there are listed items. This
precludes that there must be no empty cells between the header and last
item in that column, and the header row is Row1. So here's what you
need to enter in the Defined Name dialog:

Name * * * * * RefersTo
Item_Hdr * * * =$A$1
Items * * * * *=OFFSET(Item_Hdr,1,0,COUNTA($A:$A)-1,1)
ItemIn_Hdr * * =$C$1
ItemsOut * * * =OFFSET(ItemIn_Hdr,1,0,COUNTA(Items),1)
Overlaps * * * =IF(AND(ItemIn="",COUNTIF(ItemsOut,"")1),Duration ,"")
TotalOverlap * =$F$1
CurrentOverlap =IF(COUNTIF(ItemsOut,"")1,SUM($E:$E),"")

Be sure to prefix the name with the sheet tab name so that they are
local to the sheet they're being used on.

In the Overlap column we will use this formula: "=Overlaps". What it
does is it checks to see if the item is out AND if there's other items
out at the same time. If so then it retrieves the value in Duration for
that item, else it returns an empty string if this is the only item
out.

You should format columns Duration, Overlap, and cell named
TotalOverlap as:
* Category=Custom, Type="[h]:mm;@" (minus the double quotes)

The code:
Sub LogItemsOut() 'Shortcut=Ctrl+o
* Dim c As Range
* For Each c In Selection.Rows
* * Cells(c.Row, Range("ItemOut").Column).Value = Now()
* * Cells(c.Row, Range("Duration").Column).Formula = "=TimeOut"
* * Cells(c.Row, Range("Overlap").Column).Formula = "=Overlaps"
* Next
End Sub

Sub LogitemsIn() 'Shortcut=Ctrl+i
* Dim c As Range
* For Each c In Selection.Rows
* * Cells(c.Row, Range("Duration").Column).Value = _
* * * *Cells(c.Row, Range("Duration").Column).Value
* * Cells(c.Row, Range("ItemIn").Column).Value = Now()
* Next
End Sub

Both procs support multiple row selection so that you can process
groups of items with a single keyboard shortcut.

The LogItemsOut() proc sets up ItemOut date&time and your formulas for
Duration and Overlap, so there's no copying or FillDown required.

The LogItemsIn() proc sets the Duration formula results to constant
values, and sets ItemIn date&time. If there is still 2 or more items
out then the commulative overlap total will persist to display, and
thus be calculated in TotalOverlap ($F$1).

<Summary
- We have a list of local defined names (13) for ranges and formulas.
- We use the formula "=TimeOut" in the ItemIn column.
- We use the formula "=Overlaps" in the Overlap column. This formula
persists so the CurrentOverlap formula includes it when subsequent
items are logged out.
- We enter ItemOut using a keyboard shortcut "Ctrl+o" to run the
"LogItemOut" proc.
- We enter ItemIn using the keyboard shortcut "Ctrl+i" to run the
"LogItemIn" proc.
- The only input required by you is the entry for Item.
- You can log out/in multiple items per keyboard shortcut.
- The TotalOverlaps cell displays the sum of current overlap for 2 or
more items being out at the same time.
</Summary

If you set up a single sheet workbook as a template then you can save
files for given periods and/or have a collection of period sheets in a
single workbook, depending on how you load it.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks a lot for taking the trouble. Shall check it out and revert
back ASAP