-
• #752
It would be complex and when it breaks.....
I might not be there.
-
• #753
Perfect time for it to break, shirley... :)
-
• #754
I have people who manually entered dates on a sheet that goes out monthly. Over the formula I wrote to do it automatically.
-
• #755
Lock cells and data validation.
-
• #756
This is a nice solution, much better than my standard create a pivot table and lookup from there solution (although that is faster).
-
• #757
Should I spend !$150 for a year of data camp?
I think like to try and get into managing people to do data stuff.
-
• #758
COUNTIFS (not COUNTIF) could work using arrays.
They are very expensive fuctions though, and can completely murder spreadsheets.
Match and vlookup could be used with the flag at the being non-exact, but sort order of the data effects the value returned.
-
• #759
Yeah, it's one of those "I know what I want, but the time taken to do it for the appreciation given for solving problem that could be done manually is inversely proportional' situations.
The VBA I nicked looks like it'll do it, nobody needs to know how it does it. It just does it. And the spreadsheet is locked and managed by me. -
• #760
But serious, does datacamp seem like a good idea? I think so.
-
• #761
SPAM
https://www.datacamp.com/promo/cybermondayI signed up. I don't know if there are others out there in my situation of: only person on team, wanting to solve things, wanting to make progress out of current situation.
-
• #762
Good luck with it. I don't have any experience of datacamp so didn't reply to your posts on it the past couple of days. Report back!
-
• #763
I'm tempted, will spam back if I do.
Loosely related - have a look at https://www.xlwings.org/
Python for Excel. Would likely sort out a lot of the data crunching in something nicer than VBA. I've not used myself, but always thought it looked promising. -
• #764
Is this like Code Academy? I've heard some quite negative stuff about Code Academy, not heard of Data Camp before, but I'd hope it's better than CA.
-
• #765
I've used S+ for data stuff and modelling in the past - it used to talk really nicely to excel (data in S+, presentation / driving from excel). Used to be that S+ and R were really similar, though not sure if that's still the case.
-
• #766
'Python for Excel' sounds like it was invented mostly to annoy srs bzns sql coders.
-
• #767
I wonder how relevant this kind of stuff will continue to be for the everyday power-user with the way MS is pushing Excel and its various BI stuff with ease of use.
As a test the other day I managed to load 256 million lines of data into Powerpivot (from a CSV) and pivots, lookups, etc took a negligible amount of time to process.
-
• #768
BI is the way for MS now isn't it?
It's all about being like tableau/ google data studio whatever. -
• #769
So far, I'm smashing through introductory SQL.
it's quite basic, like me, but there are projects for everything. I think it's a case of doing the courses. Then doing the projects. -
• #770
I would like to open the open file dialogue using VBA from a pre specified location on a sharepoint site. I can open the open dialogue bow using GetOpenFilename. How can I make sure the open dialogue box is always pointing at the correct place?
For bonus points, can I also filter files based on the filename (with wild card)? i.e. the file name structure will be filename_yyymmdd. I'd like the user to only see files with the filename string in them.
and sorted by date for even more ageing fixeh sidder points.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-getopenfilename-method-excel
-
• #772
This will get you close
Sub OpenFile() Dim myfile As String With Application.FileDialog(msoFileDialogFilePicker) .InitialFileName = "C:\TEMP\filename*" .Show myfile = .SelectedItems(1) End With Workbooks.Open Filename:=myfile End Sub
(Obviously replace c:\temp with your directory). Have a google of msoFileDialogFilePicker for some of the other variables
Although if you really don't want them to select other files I'd be tempted to use VBA to create a sheet with a list of the files that they can open hyperlinked to the location.
-
• #773
Thankyou, will have a look if I get time tomorrow.
-
• #774
sorted by date
I reckon you would struggle to do this with .net or vb, let alone VBA.
A diiiirty way could be to have a VBA application caller insert a dword value into the registry to force the sort order, and then remove it when the sub is run. The walk away when your PC stops running.
Nice, looks about right.
I was thinking about this this morning, I think it could be formula'able, but potentially v complex. You'd also have to make an assumption of the upper limit of how many people could be off in one time
Vba is the right way i think. It will recalculate all the time, so might slow down if it or your spreadsheet gets big, or you start tracking many years of holidays, but can't imagine it'll be terribly noticeable in this case.