Posts by natrajc

This is what I managed to do:

=IF(AND(OR(E2="4 Channels"),G2>20),"15",IF(AND(OR(E2="4 Channels"),G2<18),"0",IF(AND(OR(E2="4 Channels"),G2<=20,G2>=18),"10",IF(AND(OR(E2="3 Channels"),G2>16),"15",IF(AND(OR(E2="3 Channels"),G2<14),"0",IF(AND(OR(E2="3 Channels"),G2<=16,G2>=14),"10",IF(AND(OR(E2="2 Channels"),G2>13),"15",IF(AND(OR(E2="2 Channels"),G2<11),"0",IF(AND(OR(E2="2 Channels"),G2<=13,G2>=11),"10","")))))))))

looks ungainly, though.. I'm sure the experts will have a simpler solution to this, as always..

Nat

Hello Experts!

I am trying to calculate the incentive (Points Accrued in the attached Excel file) for a staffer, based on the number of chats he handles for a particular channel count.

Though I can get it to calculate for the ONE criteria, I'm stuck trying to nest the IF and ANDs. I tried Google and other sites that suggest a solution, but I'm either too stupid to figure it out, or it doesn't cover enough tiers.

Would love to have a helping hand figuring this out.

Much thanks in advance.
Nat

John, (Had to stop myself from writing a 'Dear John' letter)

Thank you for the help; we're stuck with using Chrome. Will look into learning a bout what you've said and see if that gets me anywhere. If it doesn't, I'll be back picking your brains.

Nat

PS

Sorry for the tardy reply, was getting kidney stones removed and had stayed offline.

Hello Experts!

Was wondering if someone could help me with the following:

I need the url of any website opened by an operator to be copied into an Excel file with a date and time stamp.

The operator uses incognito mode , too, at times.

The idea of this is to be able to backtrack and find a particular operator who did not update the url (s)he opened to an excel sheet, which is what is required of the operator.

Thank you in advance.

PS

Went through a search and found five pages that pulled up for a search on "Extracting URL automatically", but was unable to make any headway.

PPS

As evidenced by my past efforts in excel/VBA, I'm not the sharpest tool in the box

Hello Tom,

That was stupid of me, DOH!

Thank you, indeed!

Nat

Hi Tom,

A quick one:

The date format in the Re-engaged on coloumn is dd/mm/yyyy

When I run the code, I see that the output results vary. Can you shed light on this? What must I change?

Cheers,
Nat

Hi Tom,

Thank you for adding the comments, certainly helped understand things a little better. As an added bonus, it made me look into the Trim formula in Excel, as well.

As you so rightly say, I find that looking at code of others helps learn. Especially when, like you, they comment so elaborately on it.

OZGrid is turning out to be a treasure trove, an absolute gem!

Many thanks.

Nat

Hello Tom!

WOW! That went right over my head, despite a liberal dose of Googling.

Having said that, it works like a charm, gives me exactly what i wanted done.

The message box and beep is a nice touch, and something for me to learn and add to my kitty.

Looks like you've spent a bit of time doing this and I am truly humbled and grateful for all the help extended by the OZGrid community. Many thanks.

I will definitely spend more time looking into the code and hope to understand what it does and how. I will then re-visit this page and hope to hear from you if I've got it right or not.

In the meanwhile, a lot more thank yous!

Cheers,
Nat

Hello Experts!

Back once again, this time with a problem I can't seem to solve at all.

Coloumn C has multiple date values, of which I'd like just the LATEST one extracted.

What I tried was to use text-to-coloumns and them use the Max function, but I'm sure there is a simpler, more elegant solution available from the experts here!

Cheers!

Carim,

Thank you, once again, for your reply.

The link you have shared has helped me learn and implement the Offset as well as the Index formulae.

The learning never stops, thank you, OZGrid!

Hello, Experts!

Back again, this time with a yearly attendance tracker issue I'm unable to solve with my limited knowledge.

The template itself is something I got off the interweb, and I was able to add the coloumns in the beginning and have the lookups work (I think).

Couloumns NU:OF is what I cant seem to get my head around, no matter how much I try fiddling with the Offset formula.

What I would like the sheet to do is:

give me the total monthly leaves in coloumn NU, total annual leaves in NV and the monthly break-up in coloumns NW:OF (the leave codes are in coloumn OI)

The holidays sheet and its functionality is something that I don't need, but I had to leave it in because it crashed the entire thing when i tried removing it.

What would be great, too, is if the experts could simplify the Offset formula for me, if it isn't too much to ask for. I get the basics, as in it looks x rows down and across, and returns the value there, but I fail to understand how that works when the calendar changes to the next month.

I did try searching on here as well as other places for a similar issue, and did run across one where the author was having issues inserting coloumns in the beginning, but that was about it, so I'm here once again.

Hope someone can shed some light on the situation.

Carim,

Thank you a MILLION times!!

This is EXACTLY what I was looking to achieve.

OZGrid experts save the day again!

I shall try and understand this by playing around with your formula.

Hello Experts!

I have been trying to get the total online time from the attached Excel sheet, but am running into issues as I'm basically no good at this.

In short: I have a sheet where Coloumn D has the following: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 170"]1 hour 2 min 46 sec[/TD]

[/tr]

[/TABLE]

coloumn C has: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 43"]online[/TD]

[/tr]

[tr]

[td]

offline

[/td]

[/tr]

[/TABLE]
What I am trying to do is get the total for the Online times for the Operator in coloumn A, for the dates in Coloumn B.

Coloumn G in the attached excel sheet will show my clumsy attempt at manipulating a formula I found online.

Hope the experts can help me out on this one.

Many thanks in advance.

Hi 01652845663,

Your solution worked like a charm!

Sorry for the late post, but a server issue at work kept me on my toes, and I could not respond.

Thank you, once again, for the help. I spent some time trying to figure out what the code does, and it makes a little more sense to me now. I'm just realising just HOW much there is I don't know. But you've given me the push I needed, to spend some more time trying to get my head around VBA.

Great forum, great people! Hats off to all those contributing so selflessly.

[IMG2=JSON]{"alt":"01652845663","data-align":"none","data-size":"full","src":"https:\/\/www.ozgrid.com\/forum\/core\/image.php?userid=329077&thumb=1&dateline=1530858315"}[/IMG2]
01652845663,

Thank you for your reply. Much appreciated.

While this does most of what I intended, it leaves the cells below E7:AI25 (below row 25, i take it?) open for editing. Any way of having these locked, as well?

Sorry for being a bugbear, but I'm not very good at VB.

Muchos Gracias!

Hello All,

Looking for a way to lock all cells before today's date.

In fact, the only cells I want people to be able to edit would be the ones with the drop-down option (data validation) already given to them.

I've tried playing around with the following bit of code I got from searching on here, but cant seem to get it working properly.

"Private Sub Workbook_Open()
Dim i As Long
With Sheets("Sheet1")
.Unprotect "test"
.Cells.Locked = False
For i = 4 To .UsedRange.Columns.Count
If .Cells(3, i) < Date Then .Columns(i).Locked = True
Next
.Protect "test"
End With
End Sub"

I'd greatly appreciate it, if someone could help me out with this.

The password for the file is "[email protected]"

Thanking you in advance,

Nat
[ATTACH]n1205186[/ATTACH]