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 »

Byers2142: May 5, 2011; Imagine I said something deep here...

Depending on which version of Excel, I'd probably say use countif formulae. Basically, if we use the example you gave and apply the column letters and row numbers:

Sheet One:

code:

  |A		|B		|C		|D		|
1 |		|Coder 1	|Coder 2	|Coder 3	|
2 |Response 1	|category	|category	|category	|
3 |Response 2	|category	|category	|category	|
4 |Response 3	|category	|category	|category	|

Sheet Two:

code:

  |A		|B		|C		|D		|
1 |		|Category 1	|Category 2	|Category 3	|
2 |Response 1	|		|		|		|
3 |Response 2	|		|		|		|
4 |Response 3	|		|		|		|

Then the formula you want to put into Sheet Two, cell B2 is:

=countif('Sheet One'!2:2,B$1)

Copy and paste that into the other cells on Sheet Two, and it will tell you how many cells in the Sheet One row match the Category heading on Sheet Two.

There are a few limitations here. First, it requires that the order of the Responses on both sheets is exactly the same. Second, it requires that the categories be precisely defined and are the actual headings of the columns on Sheet Two. If those two things are true, countif should give you what you need. If not, you'll need to get creative with matches and vlookups, I think.

# ? Feb 13, 2013 00:39

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 09:46

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

GregNorc posted:

I have a question on pivot tables (I think)

I'm working on a user study where we asked users questions, then assign their responses to one of several atomic category. (each response can only fit into one category).

To make this less subjective, four experimenters all independently categorized the responses. We're using a formula called Fless' Kappa to double check that our agreement is not due to random chance.

There is a big problem though:

Our current coding document reports coders on the X axis, issues on on y axis, and whatever relevant category is present in individual cells, like this:
code:
             coder 1     coder 2      coder 3       coder 4
-----------------------|------------|------------|-----------
Response 1 |  category |  category  |  category  |  category
Response 2 |  category |  category  |  category  |  category
Response 3 |  category |  category  |  category  |  category
We need a table where the X axis is the various categories, the Y axis is the various issues reported (issue 1, issue 2 ... issue N), and each cell has counts
code:
           | category 1 | category 2 | category 3
---------------------------------------------------
response 1 |  3         |  1         |  0
response 2 |  4         |  0         |  0
response 3 |  0         |  0         |  4
So for the 1st row, if all 3 coders felt it fit into category 1, and 1 coder felt it fell into category 2, we'd see results as I display above.

Is there a way to get from the first example to the second via pivot tables or some other automatic means?

You can do this with countifs like Byers says above, but if you're open to changing your source data structure, it's pretty trivial to make a pivot table out of it too. Here's a quick spreadsheet using your example data that does this.

The "old" tab has your data formatted as in your first example. I copied and pasted a few times to re-arrange it on the "new" tab, basically making "coder" into a generic column that was filled in with the specific record, instead of having a different column for each coder. That allowed me to consolidate your data into three columns: question, category, and coder. Then it's pretty easy to create a pivot off of this that gives you the result you're looking for, in the "new pivot" tab, using "question" on rows, "category" on columns, and count of "coder" in the values.

Depending on your data and what you will be doing with it in the future, this might be more extensible than using countifs, but it's up to you.

# ? Feb 13, 2013 01:03

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 19:18 on Jun 1, 2017

# ? Feb 13, 2013 01:09

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

GregNorc posted:

One issue: When I try and drag it (so I don't type these all by hand), it increments some bits I'd rather stay static. Is there any way to force excel not to increment part of the equation?

You can make a cell, row, or column reference absolute by putting a $ in front of it ("=$A$1" will keep both A and 1 constant), or use F4 while clicked in the formula to toggle through the various ways you can make them absolute.

# ? Feb 13, 2013 01:12

maskenfreiheit: Dec 30, 2004

maskenfreiheit fucked around with this message at 19:18 on Jun 1, 2017

# ? Feb 13, 2013 02:14

Ragingsheep: Nov 7, 2009

Is it possible to have the tool tips (?) that show up when you're entering a formula that tell you what arguments are needed appear for user defined functions?

# ? Feb 14, 2013 02:15

Byers2142: May 5, 2011; Imagine I said something deep here...

Ragingsheep posted:

Is it possible to have the tool tips (?) that show up when you're entering a formula that tell you what arguments are needed appear for user defined functions?

I've always been told no in the past, and a quick Google search suggested it's still not possible to have the tooltips pop up as you're typing the function.

# ? Feb 14, 2013 21:36

AzureSkys: Apr 27, 2003

I'm rather new to making macros and doing code type stuff, but am good at the basics of excel.

I cannot figure out how to make a macro to do what I've been doing manually.
I have two workbooks, A and B.
Workbook A has particular DATA for an ITEM (hundreds of them) that I want to add next to the same ITEM in Workbook B where the ITEMs are ordered correctly and narrowed down to just 80 of them.

Workbook B has the DATA consecutively ordered and unfortunately wbA (the source for the DATA) doesn't. It's scattered all over inside of a cell and I have to manually search to find the correct reference.

I've contacted the source for the Workbook A and they don't plan on adding anything to sort the items consecutively which makes my process the way it is now.

For now I just copy ITEM1 in wbB
Then go to wbA, ctrl F and paste ITEM1
Find the cell, go to the adjacent cell for the DATA
Copy the DATA
Then go back to wbB to paste it in the corresponding cell.

code:


Starts like so:
Workbook A:
 |A                           |B
1| WordsandNumbers ITEM4 Date |  DATA4 |
2| WordsandNumbers ITEM2 Date |  DATA2 |
3| WordsandNumbers ITEM3 Date |  DATA3 |
4| WordsandNumbers ITEM1 Date |  DATA1 |


Workbook B:
 |A      |B              |C
1|       | Sequential #1 | ITEM1 |
2|       | Sequential #2 | ITEM2 |
3|       | Sequential #3 | ITEM3 |
4|       | Sequential #4 | ITEM4 |


And I want:
Workbook A:
 |A                           |B
1| WordsandNumbers ITEM4 Date |  DATA4 |
2| WordsandNumbers ITEM2 Date |  DATA2 |
3| WordsandNumbers ITEM3 Date |  DATA3 |
4| WordsandNumbers ITEM1 Date |  DATA1 |


Workbook B:
 |A      |B              |C
1| DATA1 | Sequential #1 | ITEM1 |
2| DATA2 | Sequential #2 | ITEM2 |
3| DATA3 | Sequential #3 | ITEM3 |
4| DATA4 | Sequential #4 | ITEM4 |

If I record a macro of me copying and pasting everything it gets messed up when I paste into the Find dialogue box and won't repeat that for the next Item because it retains the text that was pasted when recording the macro. I've seen that there is specific find code for making macros, but it's beyond my comprehension at the moment. I appreciate any help.

# ? Feb 19, 2013 19:34

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

Try this

code:

Function OldJamesLookup(ItemName as string, SearchRNG as range) As String
   dim CELL as Range

   With SearchRNG
      set CELL = .find(what:=ItemName, after:=.cells(1,1), lookin:=xlvalues, lookat:=xlwhole)
   End With
   If CELL is nothing then
      OldJamesLookup = "N/A"
   Else
      OldJamesLookup = cell.range("B1").value
   End If
End Function

# ? Feb 19, 2013 23:26

Turkeybone: Dec 9, 2006

Just a random question about charts. I know that I can't directly concatenate something in a chart title, but does anyone know why? Like.. on the chart itself, I can't say:

code:

="Sales of blah blah by "&Sheet1!A1

code:

 =CONCATENATE("some ","words ","and ", Sheet1!A1)

But if I put that crap into a cell, I can reference that cell (e.g. I can make a chart title say =A1, and put my dynamic reference in A1). Does anyone know why this is, or if there's some way to concatenate in the chart title? More of a curiosity than anything.

# ? Feb 19, 2013 23:40

stuxracer: May 4, 2006

Turkeybone posted:

Just a random question about charts. I know that I can't directly concatenate something in a chart title, but does anyone know why? Like.. on the chart itself, I can't say:
code:
="Sales of blah blah by "&Sheet1!A1
or
code:
 =CONCATENATE("some ","words ","and ", Sheet1!A1)
But if I put that crap into a cell, I can reference that cell (e.g. I can make a chart title say =A1, and put my dynamic reference in A1). Does anyone know why this is, or if there's some way to concatenate in the chart title? More of a curiosity than anything.

You can do this with a tiny bit of VBA like this

code:

Private Sub Workbook_Open()
    Sheets("Sheet1").ChartObjects("Chart 1").Select
    ActiveChart.ChartTitle.Characters.Text = "Sales of blah blah by " & Range("Sheet1!A1").Value
End Sub

Not sure why it is blocked and honestly I just make the charts transparent and put the formula behind the thing so other people can actually change it.

Edit: Removed an extra line. Just rename the chart name and text/range.

stuxracer fucked around with this message at 03:46 on Feb 20, 2013

# ? Feb 20, 2013 03:41

Turkeybone: Dec 9, 2006

Yeah, I just wanna know why it can't be done right in the chart! I never would have even cared, but someone asked me why not, and now I am determined to find out why.

# ? Feb 20, 2013 04:04

Byers2142: May 5, 2011; Imagine I said something deep here...

Turkeybone posted:

Yeah, I just wanna know why it can't be done right in the chart! I never would have even cared, but someone asked me why not, and now I am determined to find out why.

Because Microsoft.

# ? Feb 20, 2013 13:41

DoleMIGHTY: Oct 23, 2007; uncle bens low fat puaddings

ok I have a p. stupid situation that I can't figure out

(I love and hate conditional formatting; i love how it makes large amounts of data easier to interpret at a glance but find setting them up to be rather cumbersome)

So I have a few columns that all describe the status of various parts. The value of the cells in these columns can either be "Y" or a date. The dates are in month/day/year format, but uncontrolled other than that (i.e. could be 01/01/2013 or 1/1/2013 or 01/01/13 depending on who inputs it).

I am trying to highlight dated cells in green. My current method, due to the looseness of the formatting, is to search for */*/*, but I can't make it work for some reason. I have considered searching for two /'s in each cell, but that seems like a clumsy way to do it.

Anyone got any ideas? This is such a simple thing but it seems to be beyond me. Example below. y's turn orange, I want the dates to be green.

code:

y	        y	        y
02/20/2013	02/20/2013	02/20/2013
2/20/2013	y	        2/20/2013
y         	y	        y
y	        y	        y
y	        2/20/13  	2/20/13
y	        y	        y
y	        2/20/13  	y

EDIT: APPARENTLY i had the formatting in those columns set to "date", I set them to text and the conditional formatting works fine with */*/*. WOOPS.

DoleMIGHTY fucked around with this message at 14:41 on Feb 21, 2013

# ? Feb 21, 2013 14:33

Ragingsheep: Nov 7, 2009

Is it possible to have two cells such that I can change the content in one cell and have the other cell mirror it and vice versa?

# ? Feb 26, 2013 13:50

Fermunky: May 30, 2003; The monkey is NOT impressed...

OK, how can I force a cell to rounddown, without it having to be done in a separate cell with a formula? I am aware of ROUNDDOWN(cell, digits), but I would prefer the rounding to happen automatically in the cell where the value is entered. Currently, the cell itself automatically rounds to the nearest. So if the cell is formatted as percentage and has 2 decimal places, 1.344 automatically becomes 1.34 and 1.345 becomes 1.35.

# ? Mar 1, 2013 14:21

Turkeybone: Dec 9, 2006

=ROUNDDOWN(1.345,2)/100

I just did that with percent formatting and it seems to do what you want? ROUNDDOWN seems to be (number, digits) not (cell, digits) as you wrote.

# ? Mar 1, 2013 14:49

The Gripper: Sep 14, 2004; i am winner

Fermunky posted:

OK, how can I force a cell to rounddown, without it having to be done in a separate cell with a formula? I am aware of ROUNDDOWN(cell, digits), but I would prefer the rounding to happen automatically in the cell where the value is entered. Currently, the cell itself automatically rounds to the nearest. So if the cell is formatted as percentage and has 2 decimal places, 1.344 automatically becomes 1.34 and 1.345 becomes 1.35.

It's not possible as a cell format, unfortunately. What you would want is truncating decimal places, which is probably omitted because it doesn't really have a common usage in spreadsheets or is uncommon enough to not warrant a cell format definition of it's own. As far as I know your only option is a separate column with ROUNDDOWN(cell, digits), or some probably ill-advised manipulation of your source data as it's imported (if it's imported).

# ? Mar 1, 2013 15:07

Fermunky: May 30, 2003; The monkey is NOT impressed...

The Gripper posted:

It's not possible as a cell format, unfortunately. What you would want is truncating decimal places, which is probably omitted because it doesn't really have a common usage in spreadsheets or is uncommon enough to not warrant a cell format definition of it's own. As far as I know your only option is a separate column with ROUNDDOWN(cell, digits), or some probably ill-advised manipulation of your source data as it's imported (if it's imported).

It is entered. Sales teams enter a rate with 3 decimal places, but our billing system only allows rates of 2 decimal places, so to not over charge a client, 1.335 MUST be entered into the system as 1.33, and the billing group manually adjusts the bill to account for the missing .005%. I guess I could force the cell to 3 decimal places and just have the team entering the rates into the system make sure they always round down. I suppose the only other way would be in vba and have an onchange event put the value into the rounddown function. Thanks!

# ? Mar 1, 2013 15:28

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Ragingsheep posted:

Is it possible to have two cells such that I can change the content in one cell and have the other cell mirror it and vice versa?

Aside from some VBA, I'm not seeing any way to get around the circular reference. Maybe make both cells references back to one master cell and just change that one? Or define one of those two cells as the master and use the other one to reference back to that one.

# ? Mar 1, 2013 22:59

Bobulus: Jan 28, 2007

I'm probably missing something incredibly obvious. I know how to set up a normal 3-color conditional scale, but this is a weird case. What I want is:

< -2 green
-2 to -1 green to white gradient according to value
-1 to 1 white
1 to 2 white to red gradient according to value
> 2 red

If you just set 0 as the midpoint, since 1 is halfway to 2, it gets colored 50% red when I want it to be start of the gradient. I tried setting up multiple 2-color gradients, but one just covers up the other. Hmm.

I suspect the solution is a formula for the midpoint, but damned if I can figure it out or google what I need to put there.

# ? Mar 3, 2013 20:13

Haydez: Apr 8, 2003; EVIL LINK

Argh, I have someone asking me how to do this in excel but I don't know much about Excel and can't get anywhere.

There's amounts of money in Column A, and "codes" in column B that have letters and numbers. (300 columns)

She wants to get the sum of entries in column A that have no code in column B
Also the sum of all entries in column a that do have them in column B.

(so basically just separate the values and sum them up in two separate spots.)

I can't figure out a way to do it with SUMPRODUCT and everything else is leading me nowhere. Any suggestions?

# ? Mar 5, 2013 22:00

Grifter: Jul 24, 2003; I do this technique called a suplex. You probably haven't heard of it, it's pretty obscure.

If I'm reading that question correctly, you should be able to do that easily with a pivot table. Go to the Insert tab, then click "Pivot Table". By default it should select all the data in your table. Click OK. You are now in a new tab with a pivot table. On the right you should see the pivot table fields list. Grab the "Codes" column header (whatever its real name is) and drag it to the "Row Labels" field on the lower right. Now the table on the upper left will have changed to a list of all the codes. Drag the "Money" column header into the "Values" field, which is right next to the "Row Labels" field. Excel will automatically generate a "Sum of Money" column in the table. Look there for an entry called (blank). The number next to it should be the sum of all the "Money" values that are next to blank rows. You can then take that from the Grand Total at the bottom to determine the sum of the non-blank money amounts.

I hope I read your question right, I am worried that if you know what SUMPRODUCT is you may have already considered the pivot table solution and it doesn't meet your needs for some reason.

# ? Mar 5, 2013 22:14

Ragingsheep: Nov 7, 2009

Haydez posted:

I can't figure out a way to do it with SUMPRODUCT and everything else is leading me nowhere. Any suggestions?

Does SUMIF not work?

# ? Mar 5, 2013 22:27

Haydez: Apr 8, 2003; EVIL LINK

edit:^^^ I have no idea how to use SUMIF to sum a column if another column is blank though

Awesome, thanks!

That seems to do it. I was googling around trying to help her out and saw some people mentioning using SUMPRODUCT as a way to do it but it wasn't working. Although I wish there was an easier way since she has each month on a separate worksheet and will have to delete those columns so they don't print out I guess.

# ? Mar 5, 2013 22:28

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Haydez posted:

edit:^^^ I have no idea how to use SUMIF to sum a column if another column is blank though

Awesome, thanks!

That seems to do it. I was googling around trying to help her out and saw some people mentioning using SUMPRODUCT as a way to do it but it wasn't working. Although I wish there was an easier way since she has each month on a separate worksheet and will have to delete those columns so they don't print out I guess.

SUMPRODUCT can be used similarly to SUMIF but it moves the "if" part outside of the formula, which allows for more complicated "if" conditions than SUMIF can handle. You could put in column C an "if" condition like:

IF(ISBLANK(B1),0,1)

then the SUMPRODUCT of columns A and C are the sum of the entries corresponding to non-blank B values. If this still doesn't work, maybe those entries aren't truly blank (like a single space, or some other non-print character, etc). Make sure the two ranges in SUMPRODUCT are the same size.

Depending on how well-structured your sheets are, there may be a (relatively) simple solution using the ADODB library in VBA to query all those sheets in one go, but this is probably not worth it unless you have like a million sheets to go through.

# ? Mar 6, 2013 02:55

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

Haydez posted:

edit:^^^ I have no idea how to use SUMIF to sum a column if another column is blank though

Awesome, thanks!

That seems to do it. I was googling around trying to help her out and saw some people mentioning using SUMPRODUCT as a way to do it but it wasn't working. Although I wish there was an easier way since she has each month on a separate worksheet and will have to delete those columns so they don't print out I guess.

This will sum values in column A when the matching row in column B is blank.

code:

=sumif(B:B,"",A:A)

This will sum the values in column A when the matching row in column D has any value.

code:

=sumif(B:B,"<>",A:A)

# ? Mar 6, 2013 05:23

Boston George: Apr 19, 2005; I learned the alphabet the hard way. DEA.HIV.IOU.

I've created a spreadsheet with clients being targeted to sign a contract. In the sheet I've created a data validation drop down list to indicate their current status;

Signed
Pending
Declined

Alongside this I'm also running a column tallying the number of contracts signed. I want to create a formula that enters a count in this cell when I select one of the options from the drop down list. E.g.

A1 A2 A3 A4 A5
Signed Pending Declined Customer Name Signed Contract
1 0 0 Customer 1 Signed
0 1 0 Customer 2 Pending

The A5 column is the one with the drop down list. I want the status of drop down list to change the number under the corresponding column; e.g if I select 'Signed' on A5, it will reflect a count of 1 in the A1 list.

Any idea how to do this?

# ? Mar 6, 2013 14:17

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

Boston George posted:

I've created a spreadsheet with clients being targeted to sign a contract. In the sheet I've created a data validation drop down list to indicate their current status;

Signed
Pending
Declined

Alongside this I'm also running a column tallying the number of contracts signed. I want to create a formula that enters a count in this cell when I select one of the options from the drop down list. E.g.

A1 A2 A3 A4 A5
Signed Pending Declined Customer Name Signed Contract
1 0 0 Customer 1 Signed
0 1 0 Customer 2 Pending

The A5 column is the one with the drop down list. I want the status of drop down list to change the number under the corresponding column; e.g if I select 'Signed' on A5, it will reflect a count of 1 in the A1 list.

Any idea how to do this?

In your example it looks like you confused columns and rows so I made the table below.

code:

  |        A        |        B        |        C        |        D        |        E        |
1 |    [cell A1]    |    [cell B1]    |    [cell C1]    |    [cell D1]    |    [cell E1]    |
2 |     Signed      |     Pending     |    Declined     |  Customer Name  | Signed Contract |
3 |        1        |        0        |        0        |   Customer 1    |     Signed      |
4 |        0        |        1        |        0        |   Customer 2    |    Pending      |

So, it sounds like you have a combobox (via data validation instead of developer object) in [cell E1]. But I am unclear if you want your selection in that cell to return a count values in [cell A1], or hide the rows that do not match your selection.

If you want to return a value in [cell A1] then you would use =countif($E$3:$E$65535,$E$1) or =sumif($E$3:$E$65535,$E$1,$A$3:$A$65535).

If you want to hide the rows that don't match column E, then you can remove your combobox - I would delete the entire row. Highlight your data and either go to the Insert Tab and clicking the insert table button, or go to the data tab and click on the filter button. This will make the column labels (signed, customer name, signed contract, etc) into pull down menus where you can choose your filter and any row that doesn't match is automatically hidden.

# ? Mar 6, 2013 14:40

Boston George: Apr 19, 2005; I learned the alphabet the hard way. DEA.HIV.IOU.

Old James posted:

In your example it looks like you confused columns and rows so I made the table below.
code:
  |        A        |        B        |        C        |        D        |        E        |
1 |    [cell A1]    |    [cell B1]    |    [cell C1]    |    [cell D1]    |    [cell E1]    |
2 |     Signed      |     Pending     |    Declined     |  Customer Name  | Signed Contract |
3 |        1        |        0        |        0        |   Customer 1    |     Signed      |
4 |        0        |        1        |        0        |   Customer 2    |    Pending      |
So, it sounds like you have a combobox (via data validation instead of developer object) in [cell E1]. But I am unclear if you want your selection in that cell to return a count values in [cell A1], or hide the rows that do not match your selection.

If you want to return a value in [cell A1] then you would use =countif($E$3:$E$65535,$E$1) or =sumif($E$3:$E$65535,$E$1,$A$3:$A$65535).

If you want to hide the rows that don't match column E, then you can remove your combobox - I would delete the entire row. Highlight your data and either go to the Insert Tab and clicking the insert table button, or go to the data tab and click on the filter button. This will make the column labels (signed, customer name, signed contract, etc) into pull down menus where you can choose your filter and any row that doesn't match is automatically hidden.

=sumif did the trick... thanks mate!

# ? Mar 8, 2013 06:18

borodino: Jul 31, 2012

Anyone know a good book/resource for learning about VBA and Macros for Excel?

# ? Mar 8, 2013 14:00

Turkeybone: Dec 9, 2006

I learned using books by John Walkenbach.. he has some giant biblical type excel book, but I think he also penned VBA for dummies.

# ? Mar 8, 2013 18:07

Tortilla Maker: Dec 13, 2005; Un Desmadre A Toda Madre

I am trying to export a MS Access table into Excel via a form button. The following code works perfectly providing my table only has one row of data. I've made a note on the line that seems to be causing the error.

code:


Private Sub Command45_Click()
'Set reference to Microsoft Excel Object library
'Set reference to Microsoft ActiveX DataObject 2.x
Const sFileNameTemplate As String = "C:\Documents and Settings\ \Template 1.0.xlsx"
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim vData As Variant
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from ExportToAccess" 'This has to be the name of the query your report is using to display data
With objRs
    .Open sSQL, objConn, adOpenStatic, adLockReadOnly
    vData = .GetRows()
    .Close
End With
With oExcel
    .Visible = True
               'Create new workbook from the template file
                Set WB = .Workbooks.Add(sFileNameTemplate)
                
                With WB
                     Set WS = WB.Worksheets("ExportToAccess") 'Replace with the name of actual sheet
                     With WS
                              Set rng = .Range("A2") 'Starting point of the data range
                              rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData) '//This is the line that seems to be causing the error. 
                                     
                     End With
 
                End With


End With
 
'clean up
Set oExcel = Nothing
Set objRs = Nothing
Set objConn = Nothing
Set vData = Nothing
End Sub

Tortilla Maker fucked around with this message at 14:23 on Mar 14, 2013

# ? Mar 14, 2013 14:19

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

Tortilla Maker posted:

code:


Private Sub Command45_Click()
'Set reference to Microsoft Excel Object library
'Set reference to Microsoft ActiveX DataObject 2.x
Const sFileNameTemplate As String = "C:\Documents and Settings\ \Template 1.0.xlsx"
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim vData As Variant
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from ExportToAccess" 'This has to be the name of the query your report is using to display data
With objRs
    .Open sSQL, objConn, adOpenStatic, adLockReadOnly
    vData = .GetRows()
    .Close
End With
With oExcel
    .Visible = True
               'Create new workbook from the template file
                Set WB = .Workbooks.Add(sFileNameTemplate)
                
                With WB
                     Set WS = WB.Worksheets("ExportToAccess") 'Replace with the name of actual sheet
                     With WS
                              Set rng = .Range("A2") 'Starting point of the data range
                              rng.Resize(UBound(vData, 2) + 1, UBound(vData, 1) + 1).Value = oExcel.WorksheetFunction.Transpose(vData) '//This is the line that seems to be causing the error. 
                                     
                     End With
 
                End With


End With
 
'clean up
Set oExcel = Nothing
Set objRs = Nothing
Set objConn = Nothing
Set vData = Nothing
End Sub

It looks like you are trying to do too much in that line. First you are resizing the size of the range, and at the same time assign values to those cells. Try breaking it up into two steps and don't forget to use the Set keyword when doing the resize.

# ? Mar 14, 2013 16:18

Tortilla Maker: Dec 13, 2005; Un Desmadre A Toda Madre

Old James posted:

It looks like you are trying to do too much in that line. First you are resizing the size of the range, and at the same time assign values to those cells. Try breaking it up into two steps and don't forget to use the Set keyword when doing the resize.

Thanks. Replaced it with different code and its working great now.

# ? Mar 14, 2013 19:19

me your dad: Jul 25, 2006

I'm posting this for my wife. She has two ranges of numbers, and needs the MIN of the two ranges, excluding any cell with "0".

She says:

I need to find the MIN for cells B2:B26 and B31:B54 (not separately but as a collective)
I do not want the MIN considered in the formula for any cell that shows �0�

I keep it this way because the report has to show 2 separate groups of data but the stats need to be collective for MIN, MAX, MEDIAN, and MODE

This is the sample data in B2:B26 and B31:54

Any idea how to do this?

code:

# ? Mar 14, 2013 19:22

Raven31: Feb 4, 2006

me your dad posted:

I'm posting this for my wife. She has two ranges of numbers, and needs the MIN of the two ranges, excluding any cell with "0".

She says:

I need to find the MIN for cells B2:B26 and B31:B54 (not separately but as a collective)
I do not want the MIN considered in the formula for any cell that shows �0�

I keep it this way because the report has to show 2 separate groups of data but the stats need to be collective for MIN, MAX, MEDIAN, and MODE

This is the sample data in B2:B26 and B31:54

Any idea how to do this?
[/code]

Quick solution is to write: =min(if(b:b>0,b:b))
Just hit ctrl+shift+enter instead of enter when you finish writing the formula in the cell. You should see {} brackets around the formula when hovering over it.

You can restrict the range to a subsection of column b like b2:b54 in your example if you want or if there is data in column b that you don't want to consider.

# ? Mar 14, 2013 19:30

EvilMayo: Dec 25, 2010; "You'll poke your anus out." - George Dubya Bush

As part of Special Olympics Healthy Athlete program we perform a variety of services for each athlete or unified partner. It has proven difficult if not impossible to digitize/record these charts because students are unable to disseminate the relevant information.

My big idea is to have a copy of the form in excel (that looks exactly like the paper form), such that a(n untrained) student can fill out the name, number, etc. fields, click a few check boxes. Then automagically when they hit "Next/Submit/Whatever" the information would be transposed onto a work sheet.

To put it another way, I would like to be able to click a button and add information from some fields on a worksheet and append them as new rows on a different worksheet, the clear the fields on the first worksheet.

(I can post a copy of the sheet if that helps)

Is there a tutorial that would fit my needs? Or a macro?

# ? Mar 15, 2013 17:12

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

XmasGiftFromWife posted:

As part of Special Olympics Healthy Athlete program we perform a variety of services for each athlete or unified partner. It has proven difficult if not impossible to digitize/record these charts because students are unable to disseminate the relevant information.

My big idea is to have a copy of the form in excel (that looks exactly like the paper form), such that a(n untrained) student can fill out the name, number, etc. fields, click a few check boxes. Then automagically when they hit "Next/Submit/Whatever" the information would be transposed onto a work sheet.

To put it another way, I would like to be able to click a button and add information from some fields on a worksheet and append them as new rows on a different worksheet, the clear the fields on the first worksheet.

(I can post a copy of the sheet if that helps)

Is there a tutorial that would fit my needs? Or a macro?

This might be better suited to Access as you can create a Form to enter data and write it directly to a database. http://office.microsoft.com/en-us/access-help/create-forms-for-a-new-database-RZ101773001.aspx

# ? Mar 15, 2013 22:39

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 09:46

EvilMayo: Dec 25, 2010; "You'll poke your anus out." - George Dubya Bush

Old James posted:

This might be better suited to Access as you can create a Form to enter data and write it directly to a database. http://office.microsoft.com/en-us/access-help/create-forms-for-a-new-database-RZ101773001.aspx

This is all nonprofit work so I am fortunate most sites have (some version) of excel. Access isn't really an option.

# ? Mar 15, 2013 22:42

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

«‹›82 »