Help me Do It Wrong
-
I have data in Sharepoint that I have linked to an excel sheet. For each row, there is qualitative data that a human has to quantify. Having quantified each row, I want the extra data to remain associated with that record. I need to generate statistics every month to show my boss2.
Is this something I can do in Excel, or do I need to move to Access? I don't want to build a package to move it to SQL Server to sit an application on top of, I want to do this dirty and business-y and entirely self-contained. I was doing it in Excel, but then I realized when I pull in a fresh month's data, it seemed to lose track of the new data I entered.
-
@Yamikuronue just generate random numbers
(helping people do things wrong is so easy, we should get more of these here)
(also, I don't know sharepoint, otherwise I would try to guess how to do it right as a bonus)
-
@fbmac said in Help me Do It Wrong:
, I don't know sharepoint
To be fair, if we're talking Excel, you can replace it with any external data source. It's basically connecting to the underlying database like it would any other.
-
What happens to the row that makes the extra data get unlinked from it? You're just keeping it all in Excel and showing it to boss2, right?
It sounds like you might just need to unlink the external data source, select all, copy, paste values, save as "2016-04.xls" and be done with that. Next month go back to your file that had the external data source, refresh it, rinse and repeat.
-
@anotherusername But I want a trendline each month showing how we're doing YTD >.>
-
@Yamikuronue copy and paste values to a new sheet, or to a single long sheet that contains every month YTD.
You weren't actually hoping to get the extra data pushed back into Sharepoint, were you? Because if so, I have NFC how that works and it's probably not even possible.
-
I imagine Access could probably do it, though, and it shouldn't really even be too terribly much work.
-
@anotherusername said in Help me Do It Wrong:
You weren't actually hoping to get the extra data pushed back into Sharepoint, were you?
No, not at all. I just want a solid setup so I can pull the latest, whip through the data in half an hour, screenshot the pivot chart, and send it to the lady who does the reports for the department.
-
@Yamikuronue yeah, once you're done whipping through the data just copy it all and paste values into the sheet that's used as the chart's data source, at the end of the previous month's data.
...you might also need to update the range that the chart references.
-
@Yamikuronue said in Help me Do It Wrong:
I have data in Sharepoint that I have linked to an excel sheet. For each row, there is qualitative data that a human has to quantify. Having quantified each row, I want the extra data to remain associated with that record. I need to generate statistics every month to show my boss2.
Is this something I can do in Excel, or do I need to move to Access? I don't want to build a package to move it to SQL Server to sit an application on top of, I want to do this dirty and business-y and entirely self-contained. I was doing it in Excel, but then I realized when I pull in a fresh month's data, it seemed to lose track of the new data I entered.
This sounds like something you can do with Excel. It's kind of what Excel is good at too.
So here's the basic Excel workflow:
- You make a worksheet that is just a "table" of the raw, normalized data.
- You build reports by making new worksheets that query the data, typically by doing aggregations (calculating statistics) or groupings (i.e., pivot tables) or both (sometimes in steps).
Pulling data from Sharepoint or a database just means that the first step is done for you implicitly.
In other words, your reports are "views" on the data. So you can treat them as such.
It does sound like the "queries" you're writing are too "specific", though.
-
@Yamikuronue said in Help me Do It Wrong:
Is this something I can do in Excel
No. There is no out-of-the-box way to have Excel change data in a SharePoint list.
As with all things SharePoint this is however not the final answer, some possible ways to get it done:
Low-tech solution: create a datasheet view and copy/past from excel back to SharePoint.
Pro: Enterprise approved
Con: Human interaction, only for SharePoint 2013 and up, the datasheet view of older versions just isn't good enough-solution: Use an Excel 2010 plug-in!
Pro: Just what you wanted
Con: euh ... it's a plug-in written for 2 versions back.-solution: Use Access
Pro: Access can make changes in a SharePoint List/Library
Con: It's Access -solution: use SSIS to extract the data to SQL server and report by pulling it back to excel. Don't forget to store the Excel on SharePoint
Pro: It works, we do it here to archive away certain data
Con: It might be slightly overkill
-
@Luhmann said in Help me Do It Wrong:
There is no out-of-the-box way to have Excel change data in a SharePoint list.
I don't need to change it in Sharepoint, that's fine. I just need it to show up in my report.
-
-
@Captain said in Help me Do It Wrong:
In other words, your reports are "views" on the data. So you can treat them as such.
So it should be okay to add columns to the table after I set up my data source? Or not? Should I make a second table that queries the first table and adds columns?
-
@Yamikuronue said in Help me Do It Wrong:
So it should be okay to add columns to the table after I set up my data source? Or not? Should I make a second table that queries the first table and adds columns?
It should be ok, as long as your "queries" don't depend on the "positions" of the columns.
I don't remember how off-hand, but you can query based on the field heading as opposed to the column number (like "A" or "B" or whatever).
-
@Yamikuronue said in Help me Do It Wrong:
I just need it to show up in my report.
Oeps ... then just add another source to the Excel and let Excel create the 'join'. You could even put it in the same Excel or a different Excel or a different SharePoint list. I have used Excel to join SharePoint lists but Access is easier because it imports the hole enchilada with linked tables.
-
@Yamikuronue said in Help me Do It Wrong:
add columns
Yes.
-
Maybe you can automatize the manual steps with Selenium or some other testing framework?
-
@Yamikuronue it generally doesn't completely blow up if you add/remove columns. I stick to adding columns to the right of the rightmost column, though. That usually doesn't cause any problems. I assume you won't be moving/adding/removing columns after you've got the thing built, though.
In fact, if you want formulas in a new column that reference the data in the table, it generally just does the right thing and fills the column with that formula if the query updates and returns a different number of rows. E.g. columns A, B, and C are returned by the data source, and the Total column, which contains a
SUM
formula and isn't part of the returned data, automatically extends/shortens to match the number of rows returned by the external data source:(there's a Start Date in
D1
and End Date inF1
that are scrolled out of view in the screenshot)
-
@Captain said in Help me Do It Wrong:
you can query based on the field heading as opposed to the column number
Yeah, using a Table.
-
Balls. I crashed Excel.
I think I have this down I guess, it just feels like I'm doing it wrong. Oh well.
Doing it wrong 1:
=IF([@[UAT Type Override]] <> "",[@[UAT Type Override]],IF([@[Test Results 2]]="","Empty",IF([@[Test Results 2]]="Approved","Bad",IF(LEN([@[Test Results 2]])<4,"Bad","Unknown"))))
Doing it wrong 2:
(the table column in P is where I put my manually curated data: the UAT Type Override column)
-
@Yamikuronue shh, don't tell anybody, but Excel crashes all. the. damn. time. when trying to load data from an external source. Basically if the data request takes more than n seconds, there's a timeout which crashes Excel.
Fortunately, you can just skid the "Excel has done a boo-boo and needs to die" all the way off the screen and continue using Excel to your heart's content.
-
@anotherusername said in Help me Do It Wrong:
you can just skid the "Excel has done a boo-boo and needs to die" all the way off the screen
It greyed out my screen saying "Excel has crashed" >.>
-
@Yamikuronue well, darn. What version of Excel and Windows? I've always just seen an annoying always-on-top window that can be moved over and Excel's still responsive underneath (once that data connection finally returns the data, naturally).
-
@anotherusername Excel 2010 on Windows 7. I think it's Windows detecting the crash, not Excel, if that makes sense? It's what Windows does when Chrome dies too.
-
@Yamikuronue Wait, a crash or a not-responding?
Not-responding greys-out the window as you describe. Crash closes the window and pops up a "check for issues" dialog.
If Excel's just not-responding, give it a few minutes before you assume it's crashed. It's not not very well-threaded.
-
@blakeyrat It offered to check for issues. But the window didn't vanish until I closed the dialog box. Maybe it was just being slow though.
I wish I'd taken a screenshot, I didn't pay that much attention. I want to say it was this one (from GIS)
-
@Yamikuronue Oh. It probably crashed then.
Well, whatever, Excel's Excel. Why don't you get your workplace to spring for a copy of Tableau, which is like 5,327,432 times better than Excel at shit like this?
-
@blakeyrat said in Help me Do It Wrong:
Why don't you get your workplace to spring for a copy of Tableau
Not enough ROI.
-
@Yamikuronue said in Help me Do It Wrong:
I think it's Windows detecting the crash, not Excel, if that makes sense?
Yes.
@Yamikuronue said in Help me Do It Wrong:
I want to say it was this one
Yes, I think that's the one. If it does that again, just move the "stopped working" window over and wait a while.
If the Excel window was also greyed out, then most likely it was simply frozen while waiting for the data to return.
-
@Yamikuronue said in Help me Do It Wrong:
I crashed Excel.
Join the club! You are no Excel user if you didn't bring it to it's knees.
-
@anotherusername said in Help me Do It Wrong:
when trying to load data from an external source.
Huh? I never had it crash whil loading the data ... it shits out after that.
-
@Yamikuronue
Is there enough ROI in bringing Office to this decade?
-
@Luhmann Probably not :(
-
@Luhmann as in, right after? Or sometime later?
It's always happened to me either while it's querying, or right after returning the data (probably while querying, but it doesn't block execution so it could potentially also return the data almost at the same instant).
-
@anotherusername
Later, it really isn't related to the fetching and updating of the data. It's more when I manipulate things. And then it works for ages without issues and then It's that Time Of The Month and it crashes on me whenever I change a , in a pivot or something.
-
@Luhmann oh, it only happens later for me when I've told it to automatically refresh the query on certain changes. Then it happens when I make those changes and it refreshes.
-
@anotherusername said in Help me Do It Wrong:
automatically refresh the query
Yeah ... I don't do that ...
In most of my cases there is no point since most of the data in the reporting SQL is only updated daily.
-
@Luhmann usually it's so that I can put parameters like Start Date and End Date into cells and have the query automatically update when I change them. That's actually rather handy sometimes.
But usually, if the update takes long enough to trigger the crash bug, the original query did too. Unless the SQL server is just PMSing at that moment.