The IT Helpdesk

[Glenryck Pilchards]

There is always a need for some technical advice on here, so I thought this would be a useful thread. 

I need help with Excel. I am currently looking after a shared document on SharePoint where my colleagues are inputting information everyday, but the tabs keep getting moved about and throwing them out of order. Is there a way of locking the tabs on the spreadsheet so it stays in order?

 

 

[Chilly McFreeze]

7 minutes ago, Glenryck Pilchards said:

There is always a need for some technical advice on here, so I thought this would be a useful thread. 

I need help with Excel. I am currently looking after a shared document on SharePoint where my colleagues are inputting information everyday, but the tabs keep getting moved about and throwing them out of order. Is there a way of locking the tabs on the spreadsheet so it stays in order?

 

 

Go to Review on the ribbon and you under the protect section there's a few different options to protect the sheet (which would be certain cells you select) or the whole workbook with a password. To protect the tabs you'll probably need to protect the whole workbook. That should mean other's can only input data and can't mess with the format.

 

[Onyx2]

4 hours ago, Glenryck Pilchards said:

I need help with Excel

I was literally going to post "who needs help with Excel" as I know my way around it. 

Chilly is right, you need to protect Workbook and check the box for "structure". If the movement of sheets is just clumsiness don't worry about a password. 

If anyone has any other Excel queries I'm game. 

Edited April 8, 2020 by Onyx2

[lars85]

Don't know if this is the best place to post, but I can't find a better thread. 

I'm shit with technology, so does someone know how I can contact Amazon any other way than their piss poor chat? I had something delivered to one of their lockers in my local Morrison's, but when I went to collect it the code was invalid. Checked with the security guard, it's the only locker they've got. Tried phoning the number on the front, told they're not taking calls and to try the online help. I got passed around about 10 different people  for over two hours on the chat. All saying they'd read the previous conversations, yet asking me the same questions and not giving a helpful answer. The last person I spoke to suggested contacting the driver. When I pointed out I didn't have any contact details for the driver and this was the only way I could communicate with Amazon, he told me to go back to the shop and ask if it had been left outside the locker. I told him it was closed now and maybe it wasn't up to me to be chasing this up. That's when I was disconnected and all of the chat conversations disappeared. 

Obviously I'm not happy and don't know how to sort this. Any advice would he much appreciated.

Edited May 1, 2020 by lars85
Missed out words

[Briefcase]

After 3 days you get a refund if the item is not collected, maybe order again?

[Tommy!]

On 4/8/2020 at 2:51 PM, Onyx2 said:

I was literally going to post "who needs help with Excel" as I know my way around it

Do you know much about VBA script for Sap GUI? 

I can get it to run but I'm assuming there is a better way to use when adding columns to custom reports before exporting than what I'm doing. 

I'd also be keen to know if there is any way I can trigger an sql stored procedure or T-SQL script from a VBA sub if you can help there. 

[Onyx2]

@Tommy!You can access a SQL database to do whatever, or write SQL directly in VBA if you add the references. PM me and I'll send you some code snippets. 

[Chris B]

I volunteer with a co-op, and we've had some issues with some other members who originally set up the website. Long story short, I've set up a .co.uk site instead of the .com site, but they're finally relinquishing their ownership of the .com website. However, it's all gone very sour, so dealing directly with them isn't the option it should be.

I'm using GoDaddy and Website builder for the .co.uk one. While I've worked with websites for years, it's only ever been from a content/copywriting and design perspective. I'm rather out of my depth here. So here's what I'm going to try to do:

1 - Either request the .com domain or, once they relinquish it, buy it.

2 - Redirect the .com domain to the new .co.uk domain.

The biggest problem here is trying to make sure the email address continues to work. So:

3 - Is there a way to set up domain-based email without paying per account to GoDaddy? Like, using Gmail or something?

4 - If I figure out that, keeping continuity of the existing .com email address. They're using RoundCube.

 

I'm trying to figure out all this, but all my google results are covered in sites that are selling or suggesting a specific product, so I'm trying to figure it out from the basics. Any basic help with ANY of the above four would be hugely appreciated.

 

 

Edited May 4, 2020 by Chris B

[Ronnie]

21 minutes ago, Chris B said:

2 - Redirect the .com domain to the new .co.uk domain.

I can help you with that once you've got possession of the .com. It involves placing a tiny bit of code on your server. That code also contains a message so that search engines know to index the new address instead of the old one.

21 minutes ago, Chris B said:

4 - If I figure out that, keeping continuity of the existing .com email address. They're using RoundCube.

Presumably you'll just want to receive email sent to that address rather than send from it because you'll be replying from a .co.uk address. The easiest solution in that case would be to set up .com to forward to an equivalent .co.uk address. You would need to own .com to do this.

[Chris B]

9 minutes ago, johnnyboy said:

I'm assuming you're thinking about using it with a free GMail account rather than a full GApps domain.  Instructions below are fairly old so it'd be worth checking with a dummy address first e.g. dummy@domainiamtesting.com -> Free GMail address I want to use.

https://medium.com/@fedorHK/how-to-setup-godaddy-domain-to-work-with-your-personal-gmail-2510bbd383fd

This is really useful, thanks.

[Chris B]

4 minutes ago, Ronnie said:

Presumably you'll just want to receive email sent to that address rather than send from it because you'll be replying from a .co.uk address. The easiest solution in that case would be to set up .com to forward to an equivalent .co.uk address. You would need to own .com to do this.

But if I own .com, I should be able to maintain consistency with an existing email address?

[Ronnie]

5 minutes ago, Chris B said:

But if I own .com, I should be able to maintain consistency with an existing email address?

Oh, if you wish choose to continue sending from the old one, sure thing. Anyway, you know how to got hold of me once you've got .com and want a little bit of guidance

[Glenryck Pilchards]

Paging Dr @Onyx2

I need a bit of help with a formula, I am trying to do a COUNTIF with IF statements in-between and I am failing miserably.

What I am attempting is to count the word Yes comparing to a person's name. 

Example data is  

A                            B

John Smith           Yes

John Smith            No

John Smith           Yes

Gary Bushell         No 

 

[Ronnie]

There are a couple of ways, depending on how you want to present your work. If you're happy to have a column C where the total is shown, then something like the following (in C1) works:

=COUNTIFS(A$1:A$4,A1,B$1:B$4,"Yes")


COUNTIFS(name range,cell on same row in name column,yes-no range,"Yes")

How you go from there is up to you. One technique is to copy and paste, using right-click > the Values icon, so that the formulas become hardcoded values. Then you would highlight the column of names and select Remove Duplicates (Expand selection) from the Data section of the ribbon, leaving you with one of each name, column B, and the total in C. Just delete B to finish with a table.

If you happen to have a list of each name already, then you just place the totals next to them to create a total. If the names are in column J:

=COUNTIFS(A$1:A$4,J1,B$1:B$4,"Yes")
  
=COUNTIFS(name range,location of name in table,yes-no range,"Yes")

 

[Frankie Crisp]

An alternative, if you're happy to display each name once and the count of yes/no in separate columns, is to put a pivot table over it.

Lay the fields out like this (A = Name, B = Result):

And you'll get your table returned like this:

Option to add in row and column totals if it's a chunky list with multiple results per person.

Edited June 18, 2020 by Frankie Crisp