Topic: Excel help

[Graeme]

If you’ve not closed the document you should be able to undo the changes?

[red_lfc_costello]

If you’ve not closed the document you should be able to undo the changes?

was closing it when i realised i didn't save it as a different name :pinch:

[red_lfc_costello]

Panic over, i remembered i emailed a copy of the excel doc to someone and found it in my sent folder!

[JC the Messiah]

Have you tried looking in your sent folder?

[Barneylfc∗]

How can I restrict the font used on a shared spreadsheet?

EG If I want every entry to be in Arial size 12, is there a setting so that no matter what size or font someone else tries to use, it will default to Arial 12?

Also, if I've created a list for certain columns, is there a setting that restricts anything else being entered in this column? Gobshites can't follow instructions properly it seems, and are ignoring the lists and entering their own things so I want to restrict the entries to the list options only.

[PaulF]

Hi Barney,
 you can use data validation to restrict entries to only items, in a predefined list.

The font thing is less straightforward. A macro could be added so when the user changes cell, or a sheet, or reopens the workbook, the font is reset to Arial 12. But we can't block it.

The macro answer is assuming you are running Excel on a windows desktop version , ie not a mac, and not the cloud version.  It may be possible to do on mac\cloud but never 100% confident.

--Edit-- if you are happy to send me the spreadsheet, I can set this up for you. PM me for my email address.

[Barneylfc∗]

Cheers mate, but it's a work spreadsheet with sensitive data so unable to forward it

I did use data validation to make the list to start with, but people are still able to manually enter whatever they want in these cells?

[PaulF]

Hi , is it the list they are able to add to? If so put the list on a separate sheet and hide the sheet.

I'll post the macro for Arial 12 later, with instructions.

Is it ok for the macro to run when the workbook opens? Or do you need to 'reset' the font more often than that?

[Barneylfc∗]

Hi , is it the list they are able to add to? If so put the list on a separate sheet and hide the sheet.

I'll post the macro for Arial 12 later, with instructions.

Is it ok for the macro to run when the workbook opens? Or do you need to 'reset' the font more often than that?

No, just the cell itself. I already have the sheet with the list hidden.

Say the LFC squad list for example is the only data I want in a specific column

1 Alisson
3 Fabinho
4 van Dijk
and so on.

I thought the data validation would have meant the user HAS to select from the dropdown only, but seems they can put

Alisson Becker
Fabinho Tavares
Virgil

Etc.

It's trivial, but I have spreadsheet OCD and fuckers not following simple instructions is annoying me, and I'm convinced they're doing it on purpose.


Had a try myself there to enter random things and it does pop up with an error, but I can copy and paste random things from another sheet, so this seems to be what the gobshites are doing.

[PaulF]

Data validation is set to Allow, from list? That's kind of the point of it .
oh, it's the copy paste. That scrolled off my screen.
Let me go look.

Google says the paste overwrites the valiation.

I can do you a macro , but will need info on where the list is and what cells you want to protect.
I'm assuming the scrotes are doing this a) to annoy you, but mainly b) cause they are lazy?
The macro will make it harder for them to enter non list values, but if they are determined there's probably a way around it.

[TepidT2O]

So you can break validation with a simple copy and paste?


Well that’s bloody annoying

[Barneylfc∗]

I'm assuming the scrotes are doing this a) to annoy you, but mainly b) cause they are lazy?

The one main culprit is just a dickhead in general.

Basically just told him if he keeps it up I'll put permissions on the sheet and block him from it. That won't annoy him, as it'll be one less job for him to do, but it'll annoy whoever has to do it instead of him.

[PaulF]

So you can break validation with a simple copy and paste?


Well that’s bloody annoying

Seems so. I was surprised.
@barney. Happy to do a macro for you. Just need a bit of time to squeeze it in .

[Barneylfc∗]

Cheers mate, will give you a shout if I need it.

[JC the Messiah]

There's another solution that I've used.  Have the sheet where they enter the data, and another sheet that's locked that is the one for viewing only (the cells in this one reference the other sheet for the data).  People can balls about with the formatting on the one they enter the data, but they can't edit the other sheet at all.

[jackh]

Anyone know if there's any way of syncing a pair of worksheets over two separate Excel workbooks?

Essentially, I want to be able to share the worksheets both with the team to which I belong - so that they have an overview of various projects - whilst being able to also share them with the various teams that the various projects are collaborations with.

[Red Raw]

Anyone know if there's any way of syncing a pair of worksheets over two separate Excel workbooks?

Essentially, I want to be able to share the worksheets both with the team to which I belong - so that they have an overview of various projects - whilst being able to also share them with the various teams that the various projects are collaborations with.

Sounds like the sort of thing that might be suited to OneDrive (if you have access to it):
https://support.microsoft.com/en-gb/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

This way there is only one version of the file so anyone with the link always has access to the latest version - you can set permissions to prevent/allow editing, start stop/sharing etc.

[Elmo!]

Anyone know if there's any way of syncing a pair of worksheets over two separate Excel workbooks?

Essentially, I want to be able to share the worksheets both with the team to which I belong - so that they have an overview of various projects - whilst being able to also share them with the various teams that the various projects are collaborations with.

There might be a better way but if it has to be 2 separate files you can use Power Query to pull data from one workbook to another (and manipulate it if you want to). Just go to the Data tab, and clcik Get Data and select From File > Workbook.

[PaulF]

I've just done something similar for a client. Using sqllite as a database to store the data and essentially using Excel as a client to read and write. 
Theoretically there's a problem if two users want to access simultaneously, but it's milliseconds for read and write.

How many tabs in each workbook?
How much data on each tab?
How many users.
How much updating compared to reading.

All of the above will influence best way.
Power query is a decent shout. Once linked the connection will hold and it's just a matter of hitting refresh. Which is fine for getting latest data, but you won't be able to write data as well from both workbooks. .. I'm assuming one workbook is a 'report'

[rob1966]

Need help with a .csv file please.

We use STORM for the phones at work and I can download an agents list in a csv file from it, but it loses the leading zeros from the phone numbers and from what I've been told by an admin, if she makes changes to the file and uploads it, it messes causes issues. So, she makes changes manually in the portal and it takes hours, as we move staff around all over the place far too much. I need to get permissions to do updates the the table via SQL, but in the meantime, to help her out, is there a way of forcing a csv to keep the leading zero she can upload it back. I'm assuming they are storing the numbers as an integer, if it was a varchar or char I'd expect the leading zeros to be retained?

Thanks.

[PaulF]

You exporting the found from Excel ( showing zeroes) to CSV which strips them?
There might be a trick, but it's a ten minute macro if not.
Shout if I've defined the problem right and I'll send you a macro.

[rob1966]

You exporting the found from Excel ( showing zeroes) to CSV which strips them?
There might be a trick, but it's a ten minute macro if not.
Shout if I've defined the problem right and I'll send you a macro.

Its coming directly from a table on the STORM Portal (accessed via the internet) and is automatically exporting and saving it as a CSV It doesn't give me an option to choose how I export it, its always a CSV

[PaulF]

Ah. So the leading zeros are in the CSV and you want to preserve them in Excel?
Also a ten minute macro

[rob1966]

Ah. So the leading zeros are in the CSV and you want to preserve them in Excel?
Also a ten minute macro

Yeah that is is. As far as I'm concerned, the file will just upload back without needing the leading zeros, its going into an Integer column, but she is an absolute basket case/IT phobic when she's changing stuff, she converts the CSV to a text file and manually edits this I therefore want her to see the data in the column with the leading zero. Eventually I will write some sql to do the amendments and run it myself.

[PaulF]

This will sort you out mate

https://www.dropbox.com/t/Ut4I1ZWLmRHnhQtz


I did try it with powershell but got bored...

[rob1966]

This will sort you out mate

https://www.dropbox.com/t/Ut4I1ZWLmRHnhQtz


I did try it with powershell but got bored...

Ta la

[Red Raw]

Need help with a .csv file please.

We use STORM for the phones at work and I can download an agents list in a csv file from it, but it loses the leading zeros from the phone numbers and from what I've been told by an admin, if she makes changes to the file and uploads it, it messes causes issues. So, she makes changes manually in the portal and it takes hours, as we move staff around all over the place far too much. I need to get permissions to do updates the the table via SQL, but in the meantime, to help her out, is there a way of forcing a csv to keep the leading zero she can upload it back. I'm assuming they are storing the numbers as an integer, if it was a varchar or char I'd expect the leading zeros to be retained?

Thanks.

Another option.

Paul may have sorted this with his macro but it sounds like it is not the CSV file as such but opening it in Excel that is the problem. Unless there are non numerical characters in the phone number (+, parentheses etc) Excel is almost certainly formatting it as a number rather than as string and stripping out leading zeros.

If you open/edit/save the csv in a text editor (e.g. Notepad++) rather than Excel it should preserve the text as is.

[PaulF]

Can you imagine a user being let loose on notepad++?
They'll think you are asking them to hack the pentagon.
Be interesting to see if Google sheets can preserve the zeroes.
Interestingly excel knows to export them in CSV. All my macro did was prefix with ' . Wasn't sure if it would export

[Elmo!]

This might help: https://provider.www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

[PaulF]

This might help: https://provider.www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

Show off

[rob1966]

This might help: https://provider.www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

I'll teach her that next week - I spoke to her about it today, my 10 yr old would have been understood better

[Red Raw]

Can you imagine a user being let loose on notepad++?
They'll think you are asking them to hack the pentagon.
Be interesting to see if Google sheets can preserve the zeroes.
Interestingly excel knows to export them in CSV. All my macro did was prefix with ' . Wasn't sure if it would export

Haha - I forget that not everyone uses text editors, although they are arguably much simpler to use than spreadsheets and word processors. 

I thought you might have chucked an apostrophe in - when I tried that and saved backwards and forwards between Excel and notepad (rather than exporting) the ' got copied back into notepad.

From time to time I get raw data from building management systems and remote sensors which all has to be beaten into shape. I swear every one of them does the formatting differently - single quotes, double quotes, mad date formatting etc. and don't get me started on byte order marks - they should shove it all right up their Big Endian!

[Andy @ Allerton!]

https://www.exceltrick.com/interesting/excel-games-free-download/

[PaulF]

https://www.exceltrick.com/interesting/excel-games-free-download/

Thanks Andy. There goes any hope of me getting work done .

[PaulF]

Haha - I forget that not everyone uses text editors, although they are arguably much simpler to use than spreadsheets and word processors. 

I thought you might have chucked an apostrophe in - when I tried that and saved backwards and forwards between Excel and notepad (rather than exporting) the ' got copied back into notepad.

From time to time I get raw data from building management systems and remote sensors which all has to be beaten into shape. I swear every one of them does the formatting differently - single quotes, double quotes, mad date formatting etc. and don't get me started on byte order marks - they should shove it all right up their Big Endian!

It's quite a challenge doing those sorts of imports. One that is rarely budgeted for either.
Power query might save some time?

[Red Raw]

It's quite a challenge doing those sorts of imports. One that is rarely budgeted for either.
Power query might save some time?

Thankfully I have access to Matlab! Power Query looks interesting though and might be useful for some of my colleagues who don't get on with Matlab.

[Lee1-6Liv]

can someone help me with a formula 

my formula IF(D7<C2,D7,"") returns an annoying zero value when D7 is a blank cell.   How can I amend this formula to return a blank "" instead.

[PaulF]

Personally I'd format the column to display zero as blank. Unless you will have zeroes?

[Riquende]

can someone help me with a formula 

my formula IF(D7<C2,D7,"") returns an annoying zero value when D7 is a blank cell.   How can I amend this formula to return a blank "" instead.

I get this a lot in things at work, and what I've been doing for years now is embedding my formula within an IF statement that checks if the cell (D7) is blank first, if it is then return a blank cell, and if it isn't then process your formula.

[PaulF]

I get this a lot in things at work, and what I've been doing for years now is embedding my formula within an IF statement that checks if the cell (D7) is blank first, if it is then return a blank cell, and if it isn't then process your formula.

IFS might make that a wee bit more readable.