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 »

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

Is there a way, when you update your excel spreadsheet, to have it upload to a table you uploaded to the web and update that table? Or do you always have to re-upload the spreadsheet data?

# ? Mar 23, 2012 01:57

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 15:05

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Bulls Hit posted:

Is there a way, when you update your excel spreadsheet, to have it upload to a table you uploaded to the web and update that table? Or do you always have to re-upload the spreadsheet data?

You can set it up to publish a range of cells as HTML every time you save the file. The just give it the path to your server.

http://office.microsoft.com/en-us/excel-help/put-excel-data-on-a-web-page-HP005256150.aspx

# ? Mar 23, 2012 09:35

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Mar 26, 2012 21:27

esquilax: Jan 3, 2003

GregNorc posted:

Is there something to SUMIF() that I'm missing?

I have some experiment data and I'm trying to do some really basic calculations in Excel instead of using R.

For example, getting the number of males in the sample.

So I wrote:

=SUMIF(C2:C26,"M")

Which allegedly gives me zero results. The data is such that in column c, males have the letter "M" and females have "F" (without quotes)... so I'm blanking on why this would fail... I thought maybe there was some invisible characters in there, but creating a test case where I know there's just M and F still gives a zero result...

Use COUNTIF(). SUMIF() tries to add all the values that equal M, and "M"+"M" = 0

# ? Mar 26, 2012 21:47

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Mar 26, 2012 21:55

do it: Jan 3, 2006; don't tell me words don't matter!

I am trying to find out how many subscriptions were active during a given period. In Column A, I have the subscription start date, in Column B, the subscription end date. Using a list of weeks, I would like to determine how many subscriptions were active in that week.

I've uploaded a sample here: http://cl.ly/3I040N1R0Z0Y1K463X1q . Because subscription XX11 started on 7/17 and ended 7/29, it was active in week 7/9-7/15, week 7/16-7/22, and week 7/23-7/29. Because subscription XY23 started 7/9 and ended 7/18, it was active in week 7/9-7/15 and week 7/16-7/22. The other weeks had no subscriptions active.

Any help would be appreciated!

# ? Mar 27, 2012 01:26

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

do it posted:

I am trying to find out how many subscriptions were active during a given period. In Column A, I have the subscription start date, in Column B, the subscription end date. Using a list of weeks, I would like to determine how many subscriptions were active in that week.

I've uploaded a sample here: http://cl.ly/3I040N1R0Z0Y1K463X1q . Because subscription XX11 started on 7/17 and ended 7/29, it was active in week 7/9-7/15, week 7/16-7/22, and week 7/23-7/29. Because subscription XY23 started 7/9 and ended 7/18, it was active in week 7/9-7/15 and week 7/16-7/22. The other weeks had no subscriptions active.

Any help would be appreciated!

code:

Function ActiveSubscription(RNG as range, START as date, LENGTH as integer)
   dim i as long
   dim last as long
   dim ENDD as date
   dim COUNTER as long

   if rng.columns.count <>2 then
      ActiveSubscription = "Invalid Range"
      Exit Function
   end if

   END = dateadd("d",LENGTH-1,START)
   last = rng.rows.count
   For i=1 to last
      if RNG.cells(i,1).value >= START AND RNG.cells(i,1) <= ENDD then
         counter = counter + 1
      elseif RNG.cells(i,2).value >= START AND RNG.cells(i,2) <= ENDD then
         counter = counter + 1
      elseif RNG.cells(i,1).value <= START AND RNG.cells(i,2) >= ENDD then
         counter = counter + 1
      end if
   Next i

   ActiveSubscription = COUNTER
End Function

Old James fucked around with this message at 04:15 on Mar 27, 2012

# ? Mar 27, 2012 03:11

do it: Jan 3, 2006; don't tell me words don't matter!

Old James posted:

code:
Function

That is awesome, thank you!

I'm also trying to figure out how I can determine which week of a calendar quarter a date is in.

For example,

code:

1/6/12     2012 Q1 WK1
7/4/11     2011 Q3 WK2

However, =WEEKNUM only gives the week in the whole year, I need it to be reset at the beginning of each quarter (1/1,3/1,7/1,10/1).

# ? Mar 27, 2012 21:56

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

do it posted:

That is awesome, thank you!

You're welcome

quote:

I'm also trying to figure out how I can determine which week of a calendar quarter a date is in.

For example,
code:
1/6/12     2012 Q1 WK1
7/4/11     2011 Q3 WK2
However, =WEEKNUM only gives the week in the whole year, I need it to be reset at the beginning of each quarter (1/1,3/1,7/1,10/1).

code:

=IF(WEEKNUM(A1)<=13,"Q1-"&WEEKNUM(A1),IF(WEEKNUM(A1)<=26,"Q2-"&MOD(WEEKNUM(A1),13),IF(WEEKNUM(A1)<=39,"Q3-"&MOD(WEEKNUM(A1),26),"Q4-"&MOD(WEEKNUM(A1),39))))

# ? Mar 28, 2012 02:02

do it: Jan 3, 2006; don't tell me words don't matter!

Old James posted:

code:

=IF(WEEKNUM(A1)<=13,"Q1-"&WEEKNUM(A1),IF(WEEKNUM(A1)<=26,"Q2-"&MOD(WEEKNUM(A1),13),IF(WEEKNUM(A1)<=39,"Q3-"&MOD(WEEKNUM(A1),26),"Q4-"&MOD(WEEKNUM(A1),39))))

This is even more awesome. However, I'm getting back 2011 Q2-0 for 6/22/11 using

code:

=(YEAR(J63)&" "&(IF(WEEKNUM(J63)<=13,"Q1-"&WEEKNUM(J63),IF(WEEKNUM(J63)<=26,"Q2-"&MOD(WEEKNUM(J63),13),IF(WEEKNUM(J63)<=39,"Q3-"&MOD(WEEKNUM(J63),26),"Q4-"&MOD(WEEKNUM(J63),39))))))

(I only added YEAR).

# ? Mar 28, 2012 13:32

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

do it posted:

This is even more awesome. However, I'm getting back 2011 Q2-0 for 6/22/11 using
code:
=(YEAR(J63)&" "&(IF(WEEKNUM(J63)<=13,"Q1-"&WEEKNUM(J63),IF(WEEKNUM(J63)<=26,"Q2-"&MOD(WEEKNUM(J63),13),IF(WEEKNUM(J63)<=39,"Q3-"&MOD(WEEKNUM(J63),26),"Q4-"&MOD(WEEKNUM(J63),39))))))
(I only added YEAR).

This version will count 7 day periods starting with the first of the year (does not align with a calendar week). I'll look into tweaking it for calendar weeks once I am out of the office.

code:

=YEAR(A1)&" "&IF(A1>=DATE(YEAR(A1),10,1),"Q4-"&TEXT(ROUNDUP((A1-DATE(YEAR(A1),10,1)+1)/7,0),"00"),IF(A1>=DATE(YEAR(A1),7,1),"Q3-"&TEXT(ROUNDUP((A1-DATE(YEAR(A1),7,1)+1)/7,0),"00"),IF(A1>=DATE(YEAR(A1),4,1),"Q2-"&TEXT(ROUNDUP((A1-DATE(YEAR(A1),4,1)+1)/7,0),"00"),"Q1-"&TEXT(ROUNDUP((A1-DATE(YEAR(A1),1,1)+1)/7,0),"00"))))

# ? Mar 28, 2012 14:55

TURTLE SLUT: Dec 12, 2005

How in the hell's bells do you get the range of the data used in a chart? As in, the range of the cells that you're using as data for your chart. Series objects have no property related to this, just XValues and poo poo like that. It seems the only way you can get it is outputting the Formula - but that returns a String :psyduck:

so you have to use regular expressions if you want to get, for example, the row number of your data.

I'm trying to make a macro for moving the data range of a chart, for context. I need the row number and I need to add or subtract to it. The unbelievably retarded workaround I made was making a column with the goddamn rownumbers and using that as the ChartTitle, so I can easily access the rownumber of a given chart.

Another small question: grouping a Button with an object does not seem to enable the Button to move with the group. When you drag the group around with the mouse it just stays in place while the rest of the group moves as normal. Is this normal?

# ? Apr 4, 2012 13:14

Powdered Toast Man: Jan 25, 2005; TOAST-A-RIFIC!!!

So I'm working on a project to convert an existing Excel workbook into a much easier to use and maintain Access database. I have some general ideas about how I'm going to do this, but I'm unsure of how to get the relationships right.

Specifically it is for keeping track of job titles and their associated security rights/groups/distribution groups in Active Directory. I have lists of job titles and groups that are easily imported into Access, but the relationships are more complicated. As it stands right now the existing workbook keeps track of them in columns. Each job title has its own column; at the top of the column is the job title itself and listed below it are the groups that job title is associated with.

Can someone point me in the right direction on how to use that information to create the relationships I'll need in Access? I could do it by hand but it's quite a bit of info.

# ? Apr 4, 2012 16:40

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

Edit: Figured it out. If function ftw!

Bulls Hit fucked around with this message at 02:23 on Apr 5, 2012

# ? Apr 5, 2012 02:18

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Apr 9, 2012 16:13

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

IF EXCEL.version >= 2007 then

code:

=COUNTIFS(coding_final!$K$2:$K$4,"<>"&categories!$A$10,coding_final!$K$2:$K$4,"<>"&categories!$A$11)

ELSE

code:

=counta(coding_final!$K$2:$K$4)-countif(coding_final!$K$2:$K$4,categories!$A$10)-countif(coding_final!$K$2:$K$4,categories!$A$11)

END IF

sorry felt especially nerdy....

# ? Apr 9, 2012 17:48

G-Dub: Dec 28, 2004; The Gonz

GregNorc posted:

So I'm trying to count all the instances in a range that do not contain one of two values.

What version of Excel are you using? 2007 onwards has a COUNTIFS and SUMIFS function where multiple criteria can be specified, which might help you out.

Regarding your syntax error, in your first statement are you not missing a comma? I am sure the syntax for COUNTIF is =COUNTIF(range,criteria). You seem to have it all rolled in to one.

I also don't think you can just fire things together like "coding_final!K2:K4(NOT" together as it will think the "(NOT" is part of the range. It should be something like

=IF(NOT(OR(coding_final!K2:K4=categories!A10,coding_final!K2:K4=categories!A11)),1,0)

and even then I am not sure off the top of my head how Excel will evaluate the range=value bit. You might need to do K2, K3 and K4 separately.

EDIT: Beaten

# ? Apr 9, 2012 17:53

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Apr 9, 2012 20:35

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

In my previous example it was checking for the two criteria against the one question (column K). Since you have two questions we just have to duplicate for the second one, in the code below I am assuming the second question is in column J.

code:

=COUNTIFS(coding_final!$J$2:$J$4,"<>"&categories!$A$10,coding_final!$J$2:$J$4,"<>"&categories!$A$11,
coding_final!$K$2:$K$4,"<>"&categories!$A$10,coding_final!$K$2:$K$4,"<>"&categories!$A$11)

# ? Apr 9, 2012 22:23

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Apr 10, 2012 15:24

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

My example was only counting rows 2 through 4 since that was what you used in your original example. So the max possible with my formula would be 3 (3 rows where neither of the answers were used for both questions). When you changed the rows to look at 2 through 49 you ended up counting 34 of the 48 rows had neither answer to both questions.

I can't tell you if that is high or low without seeing the data myself. But with a sample that size you should be able to scan through it to eyeball if about 30% of the rows used one of the two answers.

# ? Apr 10, 2012 16:37

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Apr 10, 2012 19:52

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

GregNorc posted:

Aha. So I would have this report 1 if both met the critera, then sum all the rows? I see.

I eyeballed it, but it'd be nice to have a formula for follow-up study which will have way too much data to eyeball it.

Correct, this formula looks at J2 and asks "Does this say 'No Problem'?" If no then it asks "Does this say 'None Mentioned'?" if no then it looks at K2 and asks the same two questions. If it passes all 4 questions it flags it as a 'True' and counts how many were true.

This is the formula you want to use for a large dataset. I was just suggesting you eyeball the data since you didn't sound like you trusted the results to help you feel more comfortable with the formula.

# ? Apr 10, 2012 20:58

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019

# ? Apr 12, 2012 14:50

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

GregNorc posted:

Aha... then something's wrong on my end since it's returning 34... Not a huge deal, the sample was small enough I was able to do it manually.

Can you upload the file somewhere?

# ? Apr 12, 2012 17:39

TURTLE SLUT: Dec 12, 2005

Cukel posted:

Another small question: grouping a Button with an object does not seem to enable the Button to move with the group. When you drag the group around with the mouse it just stays in place while the rest of the group moves as normal. Is this normal?

After a lot of research I've concluded that if you want to move a Button on the worksheet, you better just right-click it and drag it. There just isn't another way that's possible in Excel.

I hate developing usability tools for Excel. If only my clients didn't want to use Excel for everything from adding two numbers together to Pacman.

# ? Apr 13, 2012 09:59

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Cukel posted:

After a lot of research I've concluded that if you want to move a Button on the worksheet, you better just right-click it and drag it. There just isn't another way that's possible in Excel.

I hate developing usability tools for Excel. If only my clients didn't want to use Excel for everything from adding two numbers together to Pacman.

That's odd, I just grouped a button with an image and they move as one unit.

# ? Apr 13, 2012 14:45

Powdered Toast Man: Jan 25, 2005; TOAST-A-RIFIC!!!

Just thought I'd share this in case someone else might benefit from it:

It took me a while, but I realized I could transpose the columns of data I had into rows, which were then far more usable for my purposes. I'll have to do some more work to get the relationships I want in my database, but I was at least able to export to CSV and then import that into SQL08. I learned some stuff about CSVs in the process as well, so that was beneficial.

Also, interesting feature I didn't know about before I did this: when you import a CSV using the Import Data Wizard for SQL08, there's a check box for using the first row as the column names. Very useful.

# ? Apr 19, 2012 01:36

Adraeus: Jan 25, 2008; by Y Kant Ozma Post

I just screwed up my spreadsheet somehow.

I'm using the following VBA code to automatically timestamp rows that are changed, and then automatically sort the spreadsheet.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Cells(Target.Row, 5).Value = Now
    End If
    Sheet3.Range("A2:J51").Sort _
    Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
    Key2:=Range("B1"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
End Sub

However, I deleted a row. The timestamp still works, but sorting does not. When Excel tries to sort, this error pops up:

I don't see anything wrong in the code. How do I resolve this error? Thank you.

RESOLVED: Apparently, A1 in Range() became A2 for some reason. Correcting the range resolved the error.

Adraeus fucked around with this message at 00:35 on Apr 25, 2012

# ? Apr 24, 2012 23:05

coyo7e: Aug 23, 2007; by zen death robot

Cukel posted:

How in the hell's bells do you get the range of the data used in a chart? As in, the range of the cells that you're using as data for your chart. Series objects have no property related to this, just XValues and poo poo like that. It seems the only way you can get it is outputting the Formula - but that returns a String so you have to use regular expressions if you want to get, for example, the row number of your data.

I'm trying to make a macro for moving the data range of a chart, for context. I need the row number and I need to add or subtract to it. The unbelievably retarded workaround I made was making a column with the goddamn rownumbers and using that as the ChartTitle, so I can easily access the rownumber of a given chart.

I spent several years trying to find out how to do (what sounds like) basically the same thing but with a different thought process, but in the end it turns out that you either need to make a fancy macro, or.... Just name the data range, and use the named object as the range that the chart refers to. It'll dynamically shift in size as you add elements to the range.

# ? Apr 30, 2012 21:47

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

coyo7e posted:

I spent several years trying to find out how to do (what sounds like) basically the same thing but with a different thought process, but in the end it turns out that you either need to make a fancy macro, or.... Just name the data range, and use the named object as the range that the chart refers to. It'll dynamically shift in size as you add elements to the range.

Yeah, it looks like the best you can get out of VBA is a series formula which you would then have to parse to figure out the ranges you are looking at. If you decide to go that way this page will help you with the series formula since it's likely you've never used it.

http://peltiertech.com/Excel/ChartsHowTo/ChartSeriesFormula.html

# ? Apr 30, 2012 22:45

CubanRefugee: Jul 1, 2003; El Jefe
Reppin' the Row since '26.

My googlefu has failed me.

I have an export of some trouble ticket data, one of the columns is Priority of the ticket, this field is a number, 1-4. The cell formatting for the column is general, which sets off the error checker about 'Text in field is a number'.

I can select the entire column of errors and use error checker to convert to a number, but this is a task I run every night, and I have the rest of the task automated save for this one hitch.

Is there an easy formula I can pop in that will automatically convert that entire column into numbers instead?

# ? May 1, 2012 23:19

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

CubanRefugee posted:

My googlefu has failed me.

I have an export of some trouble ticket data, one of the columns is Priority of the ticket, this field is a number, 1-4. The cell formatting for the column is general, which sets off the error checker about 'Text in field is a number'.

I can select the entire column of errors and use error checker to convert to a number, but this is a task I run every night, and I have the rest of the task automated save for this one hitch.

Is there an easy formula I can pop in that will automatically convert that entire column into numbers instead?

code:

sub convert_to_numbers()
     thisworkbook.sheets(1).range("C:C").numberformat = "0"
end sub

# ? May 1, 2012 23:51

CubanRefugee: Jul 1, 2003; El Jefe
Reppin' the Row since '26.

Old James posted:

code:

sub convert_to_numbers()
     thisworkbook.sheets(1).range("C:C").numberformat = "0"
end sub

Thanks!

That worked wonderfully on one worksheet, but it looks like Excel just hates me for this other export. If I copy the data with the source sheet open, the column won't convert, but if I close the source spreadsheet and then paste, it loses all formatting, and all my formulas work.

Excel :arghfist:

# ? May 2, 2012 01:12

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

CubanRefugee posted:

Thanks!

That worked wonderfully on one worksheet, but it looks like Excel just hates me for this other export. If I copy the data with the source sheet open, the column won't convert, but if I close the source spreadsheet and then paste, it loses all formatting, and all my formulas work.

Excel

When you paste within your macro try changing it to paste values and ignore formatting like this...

code:

Range("A1").pastespecial xlpastevalues

EDIT: VVVVV That's more than a small question. You may want to start reading up on Visual Basic and then check back if you have trouble with a specific piece of code or syntax.

Old James fucked around with this message at 20:03 on May 13, 2012

# ? May 2, 2012 01:59

Mr. Apollo: Nov 8, 2000

I have an Excel workbook that contains data on multiple financial transactions (basically put and call options). Each row is a seperate transaction and each transaction spans about 12 columns.

I have created several workbooks for other employees that use the exact same data that I have but then there are additional calculations and formula applied to the data.

The problem is that as financial transactions are removed or new ones are added I have to go into all the other workbooks and manually update them.

I would like to be able to just update my workbook and then have the other automatically do the same (i.e. adding or removing rows as necessary). Is this possible?

# ? May 13, 2012 04:16

G-Dub: Dec 28, 2004; The Gonz

This sounds like a job for a database. Is Access an option?

# ? May 16, 2012 20:54

Mr. Apollo: Nov 8, 2000

G-Dub posted:

This sounds like a job for a database. Is Access an option?

Not really. The people who will be using it only have Excel.

# ? May 17, 2012 16:43

jusion: Jan 24, 2007

Mr. Apollo posted:

Not really. The people who will be using it only have Excel.

Is Google Docs an option?

# ? May 17, 2012 19:41

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 15:05

Sonic H: Dec 8, 2004; Me love you long time

My brain has given up on this one. Hopefully a genius here can help.

Basically I'm trying to create a dynamic, dynamic named range. That is to say, I'm trying to create a dynamic named range where the (single) column in which the range sits is itself dynamic. The usual method for dynamic ranges

[code]
=OFFSET($D$6,0,MATCH(Cover!E6,Platform_Dropdown,0),COUNTA(D:D),1)
[/offset]

isn't working as the COUNTA(column:column) is the bit that needs to be dynamic. Counting which column to use is easy - that's the MATCH bit.

I'm essentially trying to create a set of linked dropdowns, second of which automatically changes based on the first one. Currently I've got a table of headings (the 1st dropdown - Platform_Dropdown, a named Range) and the options for the 2nd list as rows under each heading.

The killer is I can't use any VBA. :suicide:

I'm open to other suggestions on how to do this too - but no VBA (not my choice).

Thanks.

Edit: Problem solved although if there alternative ways of doing things, I'd be happy to hear them.

Sonic H fucked around with this message at 13:04 on May 18, 2012

# ? May 18, 2012 11:51

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

«‹›82 »