Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

double nine: Aug 8, 2013

I got it. it's not elegant, but I used

code:

=INDIRECT("a"&((ROW()-1)*3)-1)
=INDIRECT("a"&(ROW()-1)*3)
=INDIRECT("a"&(ROW()-1)*3+1)

and it works, sort of. Enough to get to work anyway.

EDIT: Excel VBA question:

I've got an excel list.

What I want to make is a button that generates a random number, then writes that number to a specific cell. If the item in the list associated with that number (e.g. the 14th item in the list) has a "1" in column C, re-roll. Do this until you get a number that doesn't return a "1" in column C.

code:

Dim mijnGetal As Integer
'comment: B2 contains the last/max number of the list; this should return a number between 1 and B2 (currently 255).  
'comment: B1 (used below) contains the list's starting cell row. 
mijnGetal = Int((Range("B2").Value - 1 + 1) * Rnd + 1)

'comment: goal: if the random number is one that has a "1" in column C, reroll until  there is a 0 or nothing in column C
Do While Range("E" & (mijnGetal + Range("b1").Value)).Value = 1

MsgBox "error, the " & Range("C" & (Range("b1").Value + mijnGetal)).Value & " item has been checked. Re-Randomizing "
mijnGetal = Int((Range("B1").Value - 1 + 1) * Rnd + 1)

Loop

'comment: prints the random number to cell.
Range("B7").Value = mijnGetal

It only sometimes seems to work, and I don't understand why.

double nine fucked around with this message at 18:47 on Jan 22, 2018

# ? Jan 22, 2018 15:40

Adbot: ADBOT LOVES YOU

# ? May 24, 2024 15:58

Looten Plunder: Jul 11, 2006; Grimey Drawer

SymmetryrtemmyS posted:

Or uploaded to pastebin or in an xls or something, yeah. Tabulated meaning the text is separated with a consistent symbol so Excel can import it.

Looten Plunder posted:

https://pastebin.com/kamnffq9

Is that what you needed?

# ? Jan 23, 2018 01:15

last laugh: Feb 11, 2004; NOOOTHING!

double nine posted:

I got it. it's not elegant, but I used
code:
=INDIRECT("a"&((ROW()-1)*3)-1)
=INDIRECT("a"&(ROW()-1)*3)
=INDIRECT("a"&(ROW()-1)*3+1)
and it works, sort of. Enough to get to work anyway.

EDIT: Excel VBA question:

I've got an excel list.

What I want to make is a button that generates a random number, then writes that number to a specific cell. If the item in the list associated with that number (e.g. the 14th item in the list) has a "1" in column C, re-roll. Do this until you get a number that doesn't return a "1" in column C.
code:
Dim mijnGetal As Integer
'comment: B2 contains the last/max number of the list; this should return a number between 1 and B2 (currently 255).  
'comment: B1 (used below) contains the list's starting cell row. 
mijnGetal = Int((Range("B2").Value - 1 + 1) * Rnd + 1)

'comment: goal: if the random number is one that has a "1" in column C, reroll until  there is a 0 or nothing in column C
Do While Range("E" & (mijnGetal + Range("b1").Value)).Value = 1

MsgBox "error, the " & Range("C" & (Range("b1").Value + mijnGetal)).Value & " item has been checked. Re-Randomizing "
mijnGetal = Int((Range("B1").Value - 1 + 1) * Rnd + 1)

Loop

'comment: prints the random number to cell.
Range("B7").Value = mijnGetal
It only sometimes seems to work, and I don't understand why.

Not exactly sure why your code doesn't do what you want -- if I understand your problem correctly Code like this might do the trick:

code:

Sub random()
Dim vnt01s As Variant
Dim intRandom As Integer
Dim i As Integer
Dim OK As Boolean

OK = False

With ActiveSheet
    vnt01s = Range(.Cells(1, 3), .Cells(255, 3)) 'column of 1's/0's
End With

Dim bln1 As Boolean
bln1 = True

'Make sure you don't enter an infinite loop

For i = 1 To UBound(vnt01s, 1)
    If vnt01s(i, 1) <> 1 Then 'termination criteria
        OK = True
    End If
Next i

If OK Then
    Do While bln1
        intRandom = WorksheetFunction.RandBetween(1, 255)
        If vnt01s(intRandom, 1) <> 1 Then bln1 = False 'termination criteria
    Loop
End If
 
 ActiveSheet.Cells(1, 2) = intRandom 'returning the value of the first selected row without a 0/1


End Sub

# ? Jan 29, 2018 04:11

sofokles: Feb 7, 2004; Fuck this

double nine posted:

code:

'comment: goal: if the random number is one that has a "1" in column C, reroll until  there is a 0 or nothing in column C
Do While Range("E" & (mijnGetal + Range("b1").Value)).Value = 1

It only sometimes seems to work, and I don't understand why.

Just a guess, typing error :

column C vs Range("E" &..

# ? Feb 13, 2018 22:18

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Does anyone fool with Power Query and Power Pivot? If so, what are your typical use cases?

I'm trying to figure out if it's worth the effort of a deep dive and learning.

Hughmoris fucked around with this message at 04:54 on Feb 23, 2018

# ? Feb 23, 2018 04:51

kloa: Feb 14, 2007

Hughmoris posted:

Does anyone fool with Power Query and Power Pivot? If so, what are your typical use cases?

I'm trying to figure out if it's worth the effort of a deep dive and learning.

I used to work with some analysts that got into it, and by proxy I sat in with them just to learn for fun.

The only reason they got into it is because one of the guys had a hard time learning SQL/DWH/SSAS, but he knew Excel well. He dove into Power stuff to be able to actually do complicated calculations that he couldn't figure out how to do in SQL :shrug:

# ? Mar 2, 2018 03:43

WHY BONER NOW: Mar 6, 2016; Pillbug

double nine posted:

I could use some assistance with this:
code:
I have data/text 			I need this to be
a					a	b	c
b					d	e	f
c					g	h	i
d
e
f
g
h
i
how do I resort this? I tried to use +sheet but excel doesn't realize that each step leaps 3 lines and I'm not going through each cell fixing them manually.

I have a similar issue...I basically want to do the exact opposite. I want to consolidate three columns into one, but have the data sorted in order:

If I had any knowledge of excel I'd try to reverse engineer the solution to the previous post...but I don't. How can I do this? Please type slowly so I understand.

# ? Mar 2, 2018 08:14

Hughmoris: Apr 21, 2007; Let's go to the abyss!

kloa posted:

I used to work with some analysts that got into it, and by proxy I sat in with them just to learn for fun.

The only reason they got into it is because one of the guys had a hard time learning SQL/DWH/SSAS, but he knew Excel well. He dove into Power stuff to be able to actually do complicated calculations that he couldn't figure out how to do in SQL

Speaking of SSAS, do you know of any recommended learning resources? I was just recently given sql/ssrs/dwh access and am trying to futz my way through but I really don't have a great learning path identified.

# ? Mar 2, 2018 18:19

kloa: Feb 14, 2007

Hughmoris posted:

Speaking of SSAS, do you know of any recommended learning resources? I was just recently given sql/ssrs/dwh access and am trying to futz my way through but I really don't have a great learning path identified.

Honestly, the MSDN is the best resource I've used so far. I don't know what background you have in SQL/databases, but I'd start here and maybe follow along or read through the tutorials below. Tabular is probably easier to understand if you know some SQL, but most DWH are probably going to be MDX based, which has a high learning curve.

https://docs.microsoft.com/en-us/sql/analysis-services/analysis-services

e: If you're more of a visual learner like myself, there are tons of YouTube channels for BI. One of my favorites is https://www.youtube.com/user/PASSBIVC

kloa fucked around with this message at 19:10 on Mar 2, 2018

# ? Mar 2, 2018 19:06

sofokles: Feb 7, 2004; Fuck this

WHY BONER NOW posted:

I have a similar issue...I basically want to do the exact opposite. I want to consolidate three columns into one, but have the data sorted in order:

If I had any knowledge of excel I'd try to reverse engineer the solution to the previous post...but I don't. How can I do this? Please type slowly so I understand.

if you in cell F3 write : =OFFSET($B$3,QUOTIENT(ROWS($F$3:$F3)-1,COLUMNS($B$3:$D$3)),MOD(ROWS($F$3:$F3)-1,COLUMNS($B$3:$D$3)))

and copy down you should get it.

What it does :

Offset($B$3, number of rows, number of columns) -> gives you back the value of the cell number of rows and columns away from B3. Locking the reference to B3 by paying dollars allows you to copy down formula - $B$3.

You want it to offset by 1 row every time you have moved 3 cells down, and to offset 0,1,2,0,1,2,0,1,2 etc columns cyclically when copying the formula down.

So to achieve that :

substituting number of rows -> w Quotient(Numerator, Denominator) -> integer division -> where the numerator is the (number of rows from F3 to the current cell) -1, and the denominator is the number of columns in the data area. -> 0/3 = 0, 1/3 = 0, 2/3 = 0, 3/3 = 1 , 4/3 = 1 ... etc.

substituting number of columns -> w MOD( Number, Divisor) -> remainder -> where the Number is the (number of rows from F3 to the current cell) -1, and the Divisor is the number of columns in the data area -> MOD(0,3) = 0, MOD(1,3) = 1, MOD(2,3) = 2, MOD(3,3) = 0, MOD(4,3) = 1, MOD(5,3) = 2, ... etc

both substitutions use the ROWS and COLUMNS functions

ROWS(RANGE) returns the number of rows in that range -> ROWS(F3:F3) = 1 , ROWS(F3:F4) = 2, ROWS(F3:F5) = 3 ... etc

COLUMNS(RANGE) works the same way

edits done

sofokles fucked around with this message at 01:50 on Mar 3, 2018

# ? Mar 2, 2018 19:18

WHY BONER NOW: Mar 6, 2016; Pillbug

Ah, this is perfect! It worked for my purposes, and after reading your writeup about 10 times, I think I actually learned a little bit too

Thanks a ton!

# ? Mar 2, 2018 20:17

Kibayasu: Mar 28, 2010

I've got a bunch of spreadsheets, some of them have a cell that is set to =SUM(E64:E65) but others have the same cell set to =SUM(E64:E66). I'm wondering if there's a way to have another cell look at that formula in the first cell and then display a text string based on the formula it sees. Or even just part of the formula. So if the formula in cell E70 is "=SUM(E64:E65)" then display XXXX in cell F70 and if the formula in E70 is "=SUM(E64:E66)" then display YYYY in cell F70.

While every spreadsheet does have a value for E64 through E66 I can't use the sum of each formula to determine the text because the value in each cell may be 0 (so E64:E65 could equal E64:E66 in some cases).

# ? Mar 15, 2018 23:25

KernelSlanders: May 27, 2013; Rogue operating systems on occasion spread lies and rumors about me.

I think you're looking for the FORMULATEXT function.

# ? Mar 16, 2018 02:23

Kibayasu: Mar 28, 2010

KernelSlanders posted:

I think you're looking for the FORMULATEXT function.

Looks like that would be exactly what I want if I wasn't using the old Excel 2007 on this office computer

Thanks though.

If there isn't a way it's at least a minor data point.

Kibayasu fucked around with this message at 23:41 on Mar 16, 2018

# ? Mar 16, 2018 19:57

sofokles: Feb 7, 2004; Fuck this

Kibayasu posted:

Looks like that would be exactly what I want if I wasn't using the old Excel 2007 on this office computer Thanks though.

If there isn't a way it's at least a minor data point.

you could make a UDF - user defined function

sceleton (works out of the box on a single cell, but no error catching) :

Function getFormulaText(rng As Range) As String

Dim str As String

str = CStr(rng.Formula)

getFormulaText = str

End Function

Would work like the formulatext function

id do a writeup on where to put this, but I cant remember 2007, so i had to google "excel 2007 enable developer tab", found it, just do what google tells you. Thereafter go to Visual Basic on the developer tab, Insert a module into your workbook, and paste the above funtion into the module. It is now available as any other funtion in that particular workbook.

wow - that was a lot of effort, and i havent even told you how to make it available to you across several workbooks. Just ask

sofokles fucked around with this message at 01:16 on Mar 17, 2018

# ? Mar 17, 2018 00:12

potatocubed: Jul 26, 2012; *rathian noises*

Kibayasu posted:

I've got a bunch of spreadsheets, some of them have a cell that is set to =SUM(E64:E65) but others have the same cell set to =SUM(E64:E66). I'm wondering if there's a way to have another cell look at that formula in the first cell and then display a text string based on the formula it sees. Or even just part of the formula. So if the formula in cell E70 is "=SUM(E64:E65)" then display XXXX in cell F70 and if the formula in E70 is "=SUM(E64:E66)" then display YYYY in cell F70.

While every spreadsheet does have a value for E64 through E66 I can't use the sum of each formula to determine the text because the value in each cell may be 0 (so E64:E65 could equal E64:E66 in some cases).

Could you make a copy of the spreadsheet, change everything in Column 66 to something that would break the SUM function (a string?) and then you could tell which rows have the longer sum in?

# ? Mar 17, 2018 00:44

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

Yeah, I'd make a copy and find replace all = with null to get the formula text

# ? Mar 17, 2018 01:30

Kibayasu: Mar 28, 2010

sofokles posted:

you could make a UDF - user defined function

sceleton (works out of the box on a single cell, but no error catching) :

Function getFormulaText(rng As Range) As String

Dim str As String

str = CStr(rng.Formula)

getFormulaText = str

End Function

Would work like the formulatext function

id do a writeup on where to put this, but I cant remember 2007, so i had to google "excel 2007 enable developer tab", found it, just do what google tells you. Thereafter go to Visual Basic on the developer tab, Insert a module into your workbook, and paste the above funtion into the module. It is now available as any other funtion in that particular workbook.

wow - that was a lot of effort, and i havent even told you how to make it available to you across several workbooks. Just ask

Thanks for the effort, I really appreciate it. I'm not familiar at all with the VB side of Excel but this is doing exactly what I need it to do so thanks a lot for that. I did just try googling for the way to make user defined functions available for all workbooks and while I thought I did everything right according to the article I read (https://support.office.com/en-us/article/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f) the function doesn't appear in new workbooks. It works just fine when it is a module for individual workbooks so maybe I saved the wrong thing?

After doing the above if I open VB while in a new workbook I'll even see the module appear.

potatocubed posted:

Could you make a copy of the spreadsheet, change everything in Column 66 to something that would break the SUM function (a string?) and then you could tell which rows have the longer sum in?

Fingerless Gloves posted:

Yeah, I'd make a copy and find replace all = with null to get the formula text

I need to make the formula text available for another worksheet to reference so it needs to just be there in the original.

Kibayasu fucked around with this message at 17:13 on Mar 17, 2018

# ? Mar 17, 2018 16:48

sofokles: Feb 7, 2004; Fuck this

Kibayasu posted:

Thanks for the effort, I really appreciate it. I'm not familiar at all with the VB side of Excel but this is doing exactly what I need it to do so thanks a lot for that. I did just try googling for the way to make user defined functions available for all workbooks and while I thought I did everything right according to the article I read (https://support.office.com/en-us/article/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f) the function doesn't appear in new workbooks. It works just fine when it is a module for individual workbooks so maybe I saved the wrong thing?

After doing the above if I open VB while in a new workbook I'll even see the module appear.

If you have followed the steps to create an add-in, save as add-in, and install add-in correctly as described on the bottom of that page, it should work. -> all this means is that the function is available to you, in your instance of excel whenever you have excel open and the add-in enabled. If you save a workbook where the function is used, and send it to someone, that someone will get a #Name error wherever the function is used. So for others to use the function, you would need to insert a module containing the function into each workbook you send away. There is no easy workaround for that (apart from giving them your add-in to install).

Edit : When you say does not appear: if you mean it doesn't pop up in the insert function menu, no it wont. But if you in a cell start writing =get it should be there on the pop-up-list to select.

sofokles fucked around with this message at 18:17 on Mar 18, 2018

# ? Mar 18, 2018 18:06

Kibayasu: Mar 28, 2010

sofokles posted:

If you have followed the steps to create an add-in, save as add-in, and install add-in correctly as described on the bottom of that page, it should work. -> all this means is that the function is available to you, in your instance of excel whenever you have excel open and the add-in enabled. If you save a workbook where the function is used, and send it to someone, that someone will get a #Name error wherever the function is used. So for others to use the function, you would need to insert a module containing the function into each workbook you send away. There is no easy workaround for that (apart from giving them your add-in to install).

Edit : When you say does not appear: if you mean it doesn't pop up in the insert function menu, no it wont. But if you in a cell start writing =get it should be there on the pop-up-list to select.

I�m not sending anything to anyone else so that�s not an issue.

To start from the beginning I started a new workbook and inserted the code you provided into a module. Typing =getFormulaText into a cell in this workbook works just fine. I then save the workbook with the module as an add in file, go to excel options and follow the steps in the article to make it an active add in. I open a new workbook, double check to see if the add in is in the active list (it is), check to see if the module appears in VB (it does), start trying to use it (=getFormulaText) and it doesn�t appear in the list when I start typing �=get� etc.

I�ve saved the add in on my desktop just to see if it some issue with the default location but it still doesn�t work.

Unless you can think of something simple that be causing that I�ll probably just forget about it, it�s a minor piece of data at best.

# ? Mar 18, 2018 20:13

sofokles: Feb 7, 2004; Fuck this

Kibayasu posted:

I�m not sending anything to anyone else so that�s not an issue.

To start from the beginning I started a new workbook and inserted the code you provided into a module. Typing =getFormulaText into a cell in this workbook works just fine. I then save the workbook with the module as an add in file, go to excel options and follow the steps in the article to make it an active add in. I open a new workbook, double check to see if the add in is in the active list (it is), check to see if the module appears in VB (it does), start trying to use it (=getFormulaText) and it doesn�t appear in the list when I start typing �=get� etc.

I�ve saved the add in on my desktop just to see if it some issue with the default location but it still doesn�t work.

Unless you can think of something simple that be causing that I�ll probably just forget about it, it�s a minor piece of data at best.

Sorry, cant help you w that. I've never had any issues with add-ins not working, and thus never have had to figure out why. Sad though, because having a personal add-in is awesome. Once you get how to make a UDF - ( or google issue , copy paste answer) there are some real time savers in having an add-in.

# ? Mar 23, 2018 20:09

Kibayasu: Mar 28, 2010

So it turns out I'm just kinda dumb and the UDF and add in worked fine all along and the only thing that wasn't working was the function not showing up in the drop down list when you start typing. I still have no idea why it would show up when I manually insert a module with the code into an individual workbook and not show up when its an add in but at least it does work. I could have sworn I had tried typing it out manually before and got the #NAME? error but I bet I made a spelling error and didn't bother checking for that. Thanks to everybody.

I at least know enough to create a macro to do the typing for me for every worksheet it needs to go in.

# ? Mar 27, 2018 18:47

Leshy: Jun 21, 2004

This question is actually about Google Sheets, but they are close enough that it should work in both. Despite searching quite a bit, I have not been able to find this exact question or a proper solution.

What I want to do, is check several columns to see if a value is present in them, and if so, return the value of a certain cell in those columns (and then do something with them like SUMming or TEXTJOINing). An illustration (example sheet on Google Sheets):

code:

          Basket 1    Basket 2      Basket 3      Basket 4
Amount    10          7             4             1
Fruit 1   Apple       Strawberry    Cherry        Banana
Fruit 2   Banana      Cranberry     Grape         Pineapple
Fruit 3   Pear        Lingonberry   Starfruit
Fruit 4               Raspberry     Mango
        
Search for Fruit:           Banana  
Fruit available?            Yes
# of baskets with fruit:    11

I have a working formula for counting the total number of baskets with the chosen fruit in this example: SUM(IF(ISNUMBER(MATCH(A9,B3:B6,0)),B2,0),IF(ISNUMBER(MATCH(A9,C3:C6,0)),C2,0),IF(ISNUMBER(MATCH(A9,D3:D6,0)),D2,0),IF(ISNUMBER(MATCH(A9,E3:E6,0)),E2,0)). However, given that my real sheet has a lot more columns, this is not a great solution. This should be an Array Formula that repeats over however many columns I have, but I'm not sure how to turn this into an actually working one without the MATCH bit throwing errors. Also open to other solutions, although I would prefer to stick with formulas. It may be very easy and I'm missing something, I just do not have a lot of experience with Array Formulas. Thanks in advance for any help!

# ? Apr 6, 2018 09:01

Kibayasu: Mar 28, 2010

Kibayasu posted:

So it turns out I'm just kinda dumb and the UDF and add in worked fine all along and the only thing that wasn't working was the function not showing up in the drop down list when you start typing. I still have no idea why it would show up when I manually insert a module with the code into an individual workbook and not show up when its an add in but at least it does work. I could have sworn I had tried typing it out manually before and got the #NAME? error but I bet I made a spelling error and didn't bother checking for that. Thanks to everybody.

I at least know enough to create a macro to do the typing for me for every worksheet it needs to go in.

So I guess this isn't Excel specifically but is there anything about the code I was given or my problem:

code:

Function getFormulaText(rng As Range) As String

Dim str As String

str = CStr(rng.Formula)

getFormulaText = str

End Function

when saved as an excel add in that would make it trigger MSE and be identified as a trojan? I even added it as an ignored file and it still kept triggering a quarantine. Or is this just MSE being a poo poo.

# ? Apr 6, 2018 18:07

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

I have a map of an area in a grid system.

Certain rooms in this grid have an X and certain rooms have a Y.

What I want to find is the distance between Y and the closest X.

Is this something that can be (un)reasonably done with Excel? If not, which programming language thread should I look at?

(I was originally going to map it out in Excel and look at it visually, and maybe I'll still end up doing that, but maybe I don't have to)

# ? Apr 6, 2018 20:21

mystes: May 31, 2006

These new features seem pretty interesting: https://dev.office.com/blogs/azure-...ties-with-excel

You have to be in the "Office Insider" program in Office 365 to get this stuff now though, so I suspect it will be 5-10 years before I'll end up using a version of office with any of these features.

Also it doesn't seem like they're at all interested in making a UI for creating any of this stuff within Excel (making javascript custom functions looks like a bit of a pain based on https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview).

mystes fucked around with this message at 22:27 on May 11, 2018

# ? May 11, 2018 22:23

potatocubed: Jul 26, 2012; *rathian noises*

totalnewbie posted:

I have a map of an area in a grid system.

Certain rooms in this grid have an X and certain rooms have a Y.

What I want to find is the distance between Y and the closest X.

Is this something that can be (un)reasonably done with Excel? If not, which programming language thread should I look at?

(I was originally going to map it out in Excel and look at it visually, and maybe I'll still end up doing that, but maybe I don't have to)

It sounds like you're talking about a variant on A* pathfinding?

Or am I mistaken?

# ? May 12, 2018 12:47

KOTEX GOD OF BLOOD: Jul 7, 2012

MacOS software thread sent me here.

I have a list of book titles in Excel for Mac followed by their year, like My Tank is Fight (2006). I want it to sort alphabetically while ignoring "the" at the beginning of titles, so The Scarlet Letter would be sorted under "s." Is there no simple way to achieve this?

# ? May 28, 2018 18:36

potatocubed: Jul 26, 2012; *rathian noises*

KOTEX GOD OF BLOOD posted:

MacOS software thread sent me here.

I have a list of book titles in Excel for Mac followed by their year, like My Tank is Fight (2006). I want it to sort alphabetically while ignoring "the" at the beginning of titles, so The Scarlet Letter would be sorted under "s." Is there no simple way to achieve this?

Try a second column with a formula in it like this:

=IF(LOWER(LEFT(A1, 4)="the "), MID(A1, 5, 10000), A1)

Then if you sort by the second column, the first column will be in the order you want.

E: That goes in cell B1, if your column of titles is in column A.

# ? May 28, 2018 18:56

KOTEX GOD OF BLOOD: Jul 7, 2012

e: nevermind, I figured this out, thanks, your formula works!!

KOTEX GOD OF BLOOD fucked around with this message at 21:09 on May 28, 2018

# ? May 28, 2018 20:55

kiwid: Sep 30, 2013

Umm, so I've been tasked to research a solution to a problem that management are having with Excel permissions. They've created this elaborate Excel document that measures sales performance for the many sales reps we have and they want to be able to allow the sales rep to "authenticate" and see their own performance metrics without exposing anyone else's.

Before we go and re-create this bullshit in a webapp, is there anything out there capable of doing this? I'm not really familiar with sharepoint but can it be manipulated into doing something like this?

# ? Jun 4, 2018 17:17

nielsm: Jun 1, 2009

Outside using a macro to export a separate file per sales rep and putting individual permissions on each file, I don't think there is any way that can't be broken with an unzip program and a text editor.

# ? Jun 4, 2018 17:37

fosborb: Dec 15, 2006; Chronic Good Poster

Yes you could hack together an access database as a VBA/connection front end that pulls in your Excel data and writes to a SharePoint list. In SharePoint, you can restrict the view of a list to only show items with your name on them. There are some minor visualization tools in SharePoint that can also restrict by data owner and if you're Enterprise you may have more in your farm.

Depending on the size of your org, you may have an existing "performance management" app (likely in your call center) which would handle all of this permissioning for you + roll ups along your hierarchy + data visualization and maybe some other things. People don't usually roll those out to sales functions, but I've had lots of success doing it.

# ? Jun 4, 2018 18:03

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

An easier but less ideal solution is to split individual people's metrics into worksheets and put them all in the same workbook, then lock each sheet with a different password.

# ? Jun 4, 2018 18:24

mystes: May 31, 2006

SymmetryrtemmyS posted:

An easier but less ideal solution is to split individual people's metrics into worksheets and put them all in the same workbook, then lock each sheet with a different password.

Isn't this not at all secure?

# ? Jun 4, 2018 19:38

potatocubed: Jul 26, 2012; *rathian noises*

It has the major problem that all the data is still there. I don't know how I'd crack it if I needed to, but it strikes me as inherently insecure.

# ? Jun 4, 2018 20:22

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

Good point, disregard my suggestion.

# ? Jun 4, 2018 20:27

fosborb: Dec 15, 2006; Chronic Good Poster

It's internal metrics. How secure do they need to be?

Honestly, it's a sales dept. I'm surprised everyone's metrics aren't on a big white board in front of all of them with each person's team ranking next to each metric.

# ? Jun 4, 2018 20:29

DRINK ME: Jul 31, 2006; i cant fix avs like this because idk the bbcode - HTML IS BS MAN

I inherited some similarly stupid poo poo spreadsheet which allows people access certain areas and bounces authentication off the ActiveDirectory server but ultimately having the data in Excel means there is a risk someone will just break it and have access to everything. It�s kind of a trade off between - we made it kind of secure and we made a functional spreadsheet to provide the data they need.

The whole idea gets worse the more you think about it though. VBA isn�t secure at all because you can just replace the password with the hash of a known password and gain access, then view everything. Or if you�re properly malicious you could edit the code to log all the usernames and passwords that are typed in - because you�re doing all of this in Excel.

# ? Jun 4, 2018 20:36

Adbot: ADBOT LOVES YOU

# ? May 24, 2024 15:58

kiwid: Sep 30, 2013

Thanks for all the suggestions. I'm currently looking into PowerBi and row-level security which might solve the entire problem. The macro idea exporting it to individual files would probably be my next choice. I'm hoping I can get out of building a web app.

A password on each worksheet wouldn't work because there is 100+ sales reps.

# ? Jun 4, 2018 21:32

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »