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 »

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

hog wizard posted:

Like many of us, I have to work on Excel spreadsheets all day. I can do a lot of the basic and intermediate functions but I need help automating a process that I do to make my work a lot more efficient.

Basically, I have 3 tabs (Report, Client, and Schedule). Please take a look:

Report

Client

Schedule

I'm going to put in a drop down menu somewhere on the Report tab that lists all of the clients and then create a button that I can press to reflect the information from both Client and Schedule tabs according to whichever client's name I pick from the dropdown.

As you can see not every client has the same number of rows. So for example, if I choose Adam from the drop down menu, the Report tab first needs to show "Adam" where it says CLIENT NAME at the header, then all of Adam's data from the Client tab, and all 8 of his rows from the Schedule tab needs to be listed under the Schedule Info table. If it was for Cory, the info from the Client tab would be reflected, but only the 4 rows containing his name would listed.

I think a macro is best for this but I'm really not familiar with macros and if someone can please help me and write out a code for this that would be the greatest thing ever. I get a ton of these reports each day and making automating generating reports would make my life so much easier.

Can someone help me out with this? Thanks so much.

Seems fairly straightforward. The code logic isn't too complex, you've essentially laid it out above, so the only barrier is knowing the specific code snippets you need. You can do almost everything you need with Cell operations. Once you add a drop down and format it with your client data range, you can go to "view code", and a

code:

Sub DropDown1_Change()

End Sub

function will let you assign whatever client was selected to a variable that will inform the rest of your code which stuff to look for. This MS article has a good code snippet that talks about populating and messing with drop down boxes in VBA.

The Cells() function will do mostly everything you need (although you can also use Rows()). Define your worksheets as objects in the beginning to make messing with them easier:

code:

    Dim Report As Worksheet
    Dim Clients As Worksheet

    Set Report = ActiveSheet
    Set Clients = Worksheets(2)

ActiveSheet is obviously whatever sheet Excel is looking at at the moment, and Worksheets(index) is a numerical index of all sheets (starting with 1 on the left).

An example of a simple cell transfer from sheet to sheet using the above definitions:

code:

j = 2
For i = 5 To 14
    Report.Cells(i, 4) = Clients.Cells(2, j)
    j = j + 1
Next i

The variable length of rows to copy thing is a little trickier, but you could use a for loop that checks to make sure cells(row,1) is equal to client before copying, and ends when it's not. Something like that.

# ? Jan 12, 2012 19:32

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 11:24

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

^^^^If you are sharing this file with others and they are scared whenever the macro security bar appears, you can do it with functions.

hog wizard, I am assuming your client name dropdown is in Report!$C$1 then in your "Client Information" table you would want to have

code:

=iferror(offset(Client!$A$1,match(Report!$C$1,Client!$A:$A,0)-1,row()-row(Report!$A$3)),"")

and in the "Schedule Information" table have

code:

=if(row()-row(Report!$A$16)>countif(Schedule!$A:$A,Report!$C$1),"",iferror(offset(Schedule!$A$1,
match(Report!$C$1,Schedule!$A:$A,0)-1+row()-row(Report!$A$17),column()-column(Report!$A$17)+1),""))

For this to work the Client and Schedule tabs must be sorted by client name.

Old James fucked around with this message at 23:41 on Jan 12, 2012

# ? Jan 12, 2012 20:44

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

Powdered Toast Man posted:

Having trouble with a sheet that is pulling from an external data source. Some details:

-The file itself is generated by an automated process on the database server, and is then emailed out to a group. It is initially generated in Excel 2003 format.

-The database connection is to MSSQL2005 through Microsoft Query and is calling a stored procedure. We have verified that the database connection itself is ok, and the stored procedure is also working properly.

-The end user opens the file in Excel 2007. At this point the imported data appears to be static, but if you select the range you can see the connection and everything looks ok, however:

-Refresh options are grayed out
-Clicking on the Refresh button does nothing. Literally nothing. You click it, and nothing happens. No errors, and the data does not update.

It is also worth noting that if you open the connection in Microsoft Query it will pull data there just fine. It simply isn't getting into the sheet.

Any ideas?

I don't work with database connections very often, but doesn't the end user have to have the database connection in their "My Data Sources" folder on Windows as well as the associated driver in order to do the refresh data? Maybe the end user machine where you are running it is missing the data source file.

Powdered Toast Man posted:

EDIT:

As an additional point, we were going to try unlinking and relinking the cells, so we found this:

Excel 2007 Help File posted:
Freeze data in an Excel table
Click the worksheet that contains the Excel table from which you want to remove the data connection.
On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range from which you want to remove the data connection. The data range is then selected.
On the Tools tab, in the External Table Data group, click Unlink. The data range remains and still bears the same name, but the connection is deleted.

What the gently caress? Where is this "Tools tab" thing?

I think that means this...

# ? Jan 12, 2012 20:55

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

Old James posted:

I don't work with database connections very often, but doesn't the end user have to have the database connection in their "My Data Sources" folder on Windows as well as the associated driver in order to do the refresh data? Maybe the end user machine where you are running it is missing the data source file.

I'll check into this, thanks. I believe he was looking in ODBC for the connection.

Thank you as well for the screenshot. The table design tools are not showing up when we make the selection in this file for some reason. Dunno what's up with that.

# ? Jan 12, 2012 21:15

hog wizard: Feb 16, 2005; by angerbeet

DukAmok and Old James, thank you for those replies. They were very helpful. I really appreciate it.

Old James, it seems like if any of the cells next to the client name is blank, then it returns a value of 0. Is there a way I can make it return a blank?

# ? Jan 12, 2012 23:06

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

hog wizard posted:

DukAmok and Old James, thank you for those replies. They were very helpful. I really appreciate it.

Old James, it seems like if any of the cells next to the client name is blank, then it returns a value of 0. Is there a way I can make it return a blank?

Ah yes, if the cell is blank the offset formula returns 0. The versions below check to see if the cell is blank and return a blank value, whereas if the cell legitimately has a value of 0 of will still return 0.

code:

=iferror(if(offset(Client!$A$1,match(Report!$C$1,Client!$A:$A,0)-1,row()-row(Report!$A$3))="","",
offset(Client!$A$1,match(Report!$C$1,Client!$A:$A,0)-1,row()-row(Report!$A$3))),"")

=if(row()-row(Report!$A$16)>countif(Schedule!$A:$A,Report!$C$1),"",
iferror(if(offset(Schedule!$A$1,match(Report!$C$1,Schedule!$A:$A,0)-1+row()-row(Report!$A$17),
column()-column(Report!$A$17)+1)="","",offset(Schedule!$A$1,match(Report!$C$1,Schedule!$A:$A,0)
-1+row()-row(Report!$A$17),column()-column(Report!$A$17)+1)),""))

EDIT: added line breaks to the formulas to avoid breaking tables

# ? Jan 12, 2012 23:38

beejay: Apr 7, 2002

Hopefully this is dead simple, I just don't know what to search for - I have two spreadsheets with ID numbers, names, and departments. One sheet has all the right departments, the other sheet has the same placeholder department for every person. The sheets vary in how many times a person is listed so I can't just do a straight sort and copy type of thing. How can I get all the departments into the other sheet?

Example:

Sheet 1

code:

ID         Name      Area   Department
75         Tom B      A     Accounting
75         Tom B      B     Accounting
82         Mary H     A     Finance
91         Paul F     A     Maintenance
91         Paul F     B     Maintenance
91         Paul F     C     Maintenance
54         Mark O     A     HR

Sheet 2

code:

ID         Name      Job   Department
75         Tom B      X     GENERIC
82         Mary H     X     GENERIC
82         Mary H     Y     GENERIC
82         Mary H     Z     GENERIC
91         Paul F     X     GENERIC
91         Paul F     Z     GENERIC
54         Mark O     X     GENERIC
54         Mark O     Y     GENERIC

People are not in the same number of rows in both sheets. This actually seems somewhat similar to hog wizard's question above but I don't think I can sort my sheets, they have to stay in the same order and I just need to get the Departments to match up.

# ? Jan 12, 2012 23:54

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

beejay posted:

stuff...

Sounds like a vlookup is in your future.

code:

=iferror(vlookup($A2,Sheet1!$A:$D,4,false),"")

# ? Jan 13, 2012 00:02

hog wizard: Feb 16, 2005; by angerbeet

Fantastic. Thank you, Old James.

# ? Jan 13, 2012 00:05

beejay: Apr 7, 2002

Vlookup is working fine for this. Thank you. I'm gonna need to take a class I think at some point to brush up.

# ? Jan 13, 2012 18:30

Pimpmust: Oct 1, 2008

One small question:
I have a sheet where I roll 6 six-sided dice in pairs of 3.
Basically a bunch of RANDBETWEEN(1:6)
Now, I want to pick out the highest of the pairs
(MAX=cell1:cell3)
and if two or more "dice"/cells equals "6", then add +1 for each additional dice that show "6".

Like this:
A roll of 5, 4, 2 would present "5". (Highest picked)
A roll of 5, 6, 1 would present "6". (Highest picked)
A roll of 6, 6, 5 would equal "7" (the five, being lower than the others, is ignored)
A roll of 6, 6, 6 would equal "8" (two additional sixes for a combined +2).

Which is the part I'm kinda stumbling upon.

Something like =IF(AND(Cell1=6;Cell2=6);1+0) will check if two cells both got sixes in them and then add 1 but I don't know what command to use to check how many sixes are rolled when there are more than two dice cells involved (short of brute forcing it with several nested statements but that's not very elegant if I add more dice). Like =IF(AND(C1=6;C2=6;C3=6);2;IF(AND(C1=6;C2=6);1;IF(AND(C2=6;C3=6);1;IF(AND(C1=6;C3=6);1;0)))) will get the job done but maybe there's a better way?

Anyone know?

Pimpmust fucked around with this message at 19:24 on Jan 13, 2012

# ? Jan 13, 2012 18:34

ZerodotJander: Dec 29, 2004; Chinaman, explain!

=MAX(C1:C3)+MAX(COUNTIF(C1:C3,6)-1,0)

# ? Jan 13, 2012 19:03

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

Pimpmust posted:

stuff...

code:

=large(C1:C3,1)+countif(C1:C3,large(C1:C3,1))-1

Old James fucked around with this message at 19:18 on Jan 13, 2012

# ? Jan 13, 2012 19:16

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

Still not pulling from the data source like it's supposed to. Worthy of note: a fellow developer can use the spreadsheet perfectly on the SQL server where the spreadsheet was created, using Excel 2003. Could there be something in the file that is getting tossed out when 2007 opens it?

We know the data connection is technically working because it functions correctly in Microsoft Query...

# ? Jan 16, 2012 13:52

gwar3k1: Jan 10, 2005; Someday soon

Have you tried changing the security level to low? You should be able to run a query without having to change that, but perhaps its a function of 2003 -> 2007 security zealotry? Developer Tab > Macro security.

If you have any work involving the analysis tool-pak, I know that is different between versions of Excel, maybe that's halting execution. If you look in the VB editor in 2007, missing references (for the workbook, not the VB references) will be highlighted in blue in the project window (Ctrl+R).

e: does the remote PC map to the database in the same way that the server pc does? We have a mapped drive L:\ to allow users access to the database stuff, maybe the connection string is to C: rather than the share?

gwar3k1 fucked around with this message at 00:13 on Jan 17, 2012

# ? Jan 17, 2012 00:09

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

gwar3k1 posted:

Have you tried changing the security level to low? You should be able to run a query without having to change that, but perhaps its a function of 2003 -> 2007 security zealotry? Developer Tab > Macro security.

If you have any work involving the analysis tool-pak, I know that is different between versions of Excel, maybe that's halting execution. If you look in the VB editor in 2007, missing references (for the workbook, not the VB references) will be highlighted in blue in the project window (Ctrl+R).

e: does the remote PC map to the database in the same way that the server pc does? We have a mapped drive L:\ to allow users access to the database stuff, maybe the connection string is to C: rather than the share?

I'm looking into this now. As far as I can tell the connection string should be fine (especially since it works ok in Microsoft Query), and we've also verified that his version of SQL Native Client is good and he has the appropriate permissions/privileges on the database server.

Edit:

Old James posted:

I think that means this...

I can't seem to get this ribbon to display. I've got the developer ribbon, but not this. How do you get it?

Powdered Toast Man fucked around with this message at 15:54 on Jan 17, 2012

# ? Jan 17, 2012 15:32

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

Powdered Toast Man posted:

I can't seem to get this ribbon to display. I've got the developer ribbon, but not this. How do you get it?

You have to have a table object on the sheet and select a cell within that table for the tab to appear. When I import data in 2007 or later it automatically creates the table object for me. For the purposes of the screen shot I made one by going to the Insert tab and clicking the second button (labelled Table, NOT PivotTable).

# ? Jan 17, 2012 18:11

rivetz: Sep 22, 2000; Soiled Meat

Really hoping someone can point me in the right direction on this. I have zero experience in coding and am slogging through this project, and I can see the light at the end of the tunnel. The code is ugly and not very efficient, but it works. Just can�t see my way through this last hurdle.

I work for a telecom carrier and am creating a proposal tool to auto-generate basic internet access pricing. The product line isn�t complicated so there's really very little in the way of bells and whistles. I have a form that requests all the cust info, which populates on another tab. The user selects checkboxes as to what bandwidth they want to quote, and on what terms, as shown below:

I have it set up so that the spreadsheet generates as many rows as are needed (8M, 10M, 20M, etc) and generates columns for the various terms (24 mo, 36 mo, etc.) Each row in the final proposal is being handled by For/Next, i.e. count the number of checkboxes ticked and generate that many quote rows. Then populating the proposal with pricing should just be a series of vlookups, right?

The problem I�m having is less about syntax and more about design or approach. How best to handle the varying bandwidths? If a user selects 10M, 20M, and 50M, how does the spreadsheet know to skip the �missing� bandwidth increments (8M, 15M, 30M)? What is the most efficient way to handle something like this?

The only thing I've been able to come up with is stacks of nested If/Then statements, but that seems inefficient and I'd like the form to be scalable to incorporate more features and products down the road. Is the key that I should be logically grouping those checkboxes somehow? Currently they're just "standalone" and I'm just going, "if chkbox8M.value = true, then" etc.

Any guidance seriously, seriously appreciated.

# ? Jan 19, 2012 16:21

G-Dub: Dec 28, 2004; The Gonz

Why don't you just generate a quote for each bandwidth and term? It doesn't seem like a lot of data.

# ? Jan 19, 2012 21:05

gwar3k1: Jan 10, 2005; Someday soon

Put the checkbox values in to an array then loop the array for evaluation if it makes you feel better. You don't really need to optimise something this small.

Alternatively, you could use a function which runs through a nested if but uses Exit Function to break out before it traverses the full statement?

# ? Jan 19, 2012 23:28

rivetz: Sep 22, 2000; Soiled Meat

Couple of reasons: I intend to add quite a few additional bandwidth increments once the framework is in place. Also the stretch between bandwidth increments is going to be large enough (10M - 1G) that only a small portion will be of interest to a given customer. I also plan to add other services down the road, so I need this chunk to be a template for other products that will demand a little more customization.

# ? Jan 19, 2012 23:30

coyo7e: Aug 23, 2007; by zen death robot

Okay I'm mildly stumped, trying to import some stuff into excel so I can compare it..

I've got a few windows computers which I exported the windows update lists into text file however, they're delimited by carriage returns. I know how to declare a delimiter but the format of the text file is making excel think that it's one column, when I need to basically make each entry a unique row. It seems like this shhouldn't ultimately be too hard to do in one way or another, but I'm having trouble making excel format the data the way i want, when it imports.

I'd prefer to not have to write a macro for this although I guess I could if nobody has a quick way to split these guys into unique rows. I don't really care if the descriptor tags go into the cell with the actual data, either way would ultimately work out the same for my purposes.

They look like this:

code:

==================================================
Name              : KB2079403
Description       : Security Update for Windows XP (KB2079403)
Installed By      : SYSTEM
Installation Date : 1/31/2011
Display Version   : 1
Update Type       : Update
Application       : Windows XP
Web Link          : [url]http://support.microsoft.com/?kbid=2079403[/url]
Uninstall Command : C:\WINDOWS\$NtUninstallKB2079403$\spuninst\spuninst.exe 
Last Modified Time: 1/31/2011 1:52:11 PM
Long Description  : 
==================================================

==================================================
Name              : KB2115168
Description       : Security Update for Windows XP (KB2115168)
Installed By      : SYSTEM
Installation Date : 1/31/2011
Display Version   : 1
Update Type       : Update
Application       : Windows XP
Web Link          : [url]http://support.microsoft.com/?kbid=2115168[/url]
Uninstall Command : C:\WINDOWS\$NtUninstallKB2115168$\spuninst\spuninst.exe 
Last Modified Time: 1/31/2011 1:53:01 PM
Long Description  : 
==================================================

==================================================
Name              : KB2121546
Description       : Security Update for Windows XP (KB2121546)
Installed By      : SYSTEM
Installation Date : 1/31/2011
Display Version   : 1
Update Type       : Update
Application       : Windows XP
Web Link          : [url]http://support.microsoft.com/?kbid=2121546[/url]
Uninstall Command : C:\WINDOWS\$NtUninstallKB2121546$\spuninst\spuninst.exe 
Last Modified Time: 1/31/2011 1:50:03 PM
Long Description  : 
==================================================

# ? Jan 20, 2012 17:54

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

coyo7e posted:

Okay I'm mildly stumped, trying to import some stuff into excel so I can compare it..

I've got a few windows computers which I exported the windows update lists into text file however, they're delimited by carriage returns. I know how to declare a delimiter but the format of the text file is making excel think that it's one column, when I need to basically make each entry a unique row. It seems like this shhouldn't ultimately be too hard to do in one way or another, but I'm having trouble making excel format the data the way i want, when it imports.

I'd prefer to not have to write a macro for this although I guess I could if nobody has a quick way to split these guys into unique rows. I don't really care if the descriptor tags go into the cell with the actual data, either way would ultimately work out the same for my purposes.

Do you mean make each entry a unique column? Because when I paste your text there into Excel it makes 41 unique rows. If I'm understanding you right, you want all of those carriage returns to effectively be tabs, moving them one cell to the right instead of one cell downwards, is that right?

If so, you can just use the "Copy->Paste->Transpose" function, it'll flip everything sideways.

# ? Jan 20, 2012 18:28

coyo7e: Aug 23, 2007; by zen death robot

Thanks, I knew there was a simple solution I wasn't thinking of!

# ? Jan 20, 2012 19:37

G-Dub: Dec 28, 2004; The Gonz

Cancer Wad posted:

Couple of reasons: I intend to add quite a few additional bandwidth increments once the framework is in place. Also the stretch between bandwidth increments is going to be large enough (10M - 1G) that only a small portion will be of interest to a given customer. I also plan to add other services down the road, so I need this chunk to be a template for other products that will demand a little more customization.

If you are going to be adding new bandwidths then checkboxes are a bit static. Look in to a multi-select listbox (sorry for making things more complicated). This means to add new bandwidths all you need to do is update the source for the listbox and not actually amend the layout of the form. It's then quite straightforward to establish all the selected items in the listbox and iterate through them to do your calculations.

EDIT: Off the top of my head something like...

code:

Dim varItem as variant

For Each varItem in lstBandwidths.ItemsSelected
' Do something with lstBandwidth.ItemData(varItem)
Next

G-Dub fucked around with this message at 21:23 on Jan 20, 2012

# ? Jan 20, 2012 21:17

rivetz: Sep 22, 2000; Soiled Meat

G-Dub posted:

If you are going to be adding new bandwidths then checkboxes are a bit static. Look in to a multi-select listbox (sorry for making things more complicated). This means to add new bandwidths all you need to do is update the source for the listbox and not actually amend the layout of the form. It's then quite straightforward to establish all the selected items in the listbox and iterate through them to do your calculations.

EDIT: Off the top of my head something like...
code:
Dim varItem as variant

For Each varItem in lstBandwidths.ItemsSelected
' Do something with lstBandwidth.ItemData(varItem)
Next

This is exactly the change in approach that I was seeking but just didn't know enough about VBA to come up with myself. It's totally the solution, and your help is really appreciated.

# ? Jan 21, 2012 09:17

ejstheman: Feb 11, 2004

How do I dereference a cell that I have a text-format reference to? Like, if the function I'm looking for is called xyz, then I could get the concatenation of two random words from a wordlist that's stored in a1-a850 with:

code:

=CONCATENATE(xyz(ADDRESS(RANDBETWEEN(1,850),1)),xyz(ADDRESS(RANDBETWEEN(1,850),1)))

# ? Jan 27, 2012 02:02

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

ejstheman posted:

How do I dereference a cell that I have a text-format reference to? Like, if the function I'm looking for is called xyz, then I could get the concatenation of two random words from a wordlist that's stored in a1-a850 with:
code:
=CONCATENATE(xyz(ADDRESS(RANDBETWEEN(1,850),1)),xyz(ADDRESS(RANDBETWEEN(1,850),1)))

You're looking for "Indirect".

code:

=CONCATENATE(INDIRECT(ADDRESS(RANDBETWEEN(1,850),1)),INDIRECT(ADDRESS(RANDBETWEEN(1,850),1)))

# ? Jan 27, 2012 02:09

ejstheman: Feb 11, 2004

DukAmok posted:

You're looking for "Indirect".

code:

=CONCATENATE(INDIRECT(ADDRESS(RANDBETWEEN(1,850),1)),INDIRECT(ADDRESS(RANDBETWEEN(1,850),1)))

Fantastic! I was totally failing at google there. Much appreciated.

# ? Jan 27, 2012 02:16

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

ejstheman posted:

How do I dereference a cell that I have a text-format reference to? Like, if the function I'm looking for is called xyz, then I could get the concatenation of two random words from a wordlist that's stored in a1-a850 with:
code:
=CONCATENATE(xyz(ADDRESS(RANDBETWEEN(1,850),1)),xyz(ADDRESS(RANDBETWEEN(1,850),1)))

I know this was already answered, but I am not a fan of INDIRECT() so you can also do it with OFFSET().

code:

=CONCATENATE(XYZ(OFFSET(A1,RANDBETWEEN(1,850)-1,0)),XYZ(OFFSET(A1,RANDBETWEEN(1,850)-1,0)))

# ? Jan 27, 2012 02:38

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

Any particular reason? I use indirect just because it was the first google hit when I was trying to solve the same issue.

# ? Jan 27, 2012 03:08

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

DukAmok posted:

Any particular reason? I use indirect just because it was the first google hit when I was trying to solve the same issue.

Both INDIRECT() and OFFSET() are volatile functions which means they recalculated with every change to the workbook so extensive use of either will rape your CPU. However, of the two OFFSET() calculates faster.

INDEX() is not volatile, so the following might be a better then either of the answers we gave above.

code:

=CONCATENATE(XYZ(INDEX(A1:A850,RANDBETWEEN(1,850),1)),XYZ(INDEX(A1:A850,RANDBETWEEN(1,850),1)))

Old James fucked around with this message at 04:59 on Jan 27, 2012

# ? Jan 27, 2012 04:51

Boz0r: Sep 7, 2006; The Rocketship in action.

Is it possible to test if the first 5 characters of a string are numbers, and then remove the rest of the string?

EDIT: Also, how do I find the first non-number character in a string?

Boz0r fucked around with this message at 13:30 on Jan 27, 2012

# ? Jan 27, 2012 13:10

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

Boz0r posted:

Is it possible to test if the first 5 characters of a string are numbers, and then remove the rest of the string?

code:

=if(isnumber(left(A1,5)),left(A1,5),A1)

Boz0r posted:

EDIT: Also, how do I find the first non-number character in a string?

code:

Function NonNumber(RNG As Range)
    Dim i As Long
    Dim testint As Integer

    For i = 1 To Len(RNG.Cells(1, 1).Value)
        On Error GoTo Results
        testint = Mid(RNG.Cells(1, 1).Value, i, 1)
    Next i
    i = 0

Results:
    NonNumber = i

End Function

# ? Jan 27, 2012 15:55

Secx: Mar 1, 2003; Hippopotamus retardus

I have an Excel spreadsheet that uses a QueryTable to fetch data from a SQL Server database. It has a Command button that essentially does this:

code:

Range("A7").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

I need to make some amendments to the SQL query that the original author created, but I can't for the life of me figure out where to edit this query. If I go in Data -> Import External Data, the option to edit query is grayed out. I only have the option to create a new query.

If I go in the VBA editor, there is no code in there that is about a QueryTable or SQL (just some basic formatting stuff).

Edit: This is for Excel 2002.

Edit 2: Nevermind. Found it. I had to right-click on a cell which was defined in the QueryTable and there were options for Edit Query.

Secx fucked around with this message at 22:38 on Jan 31, 2012

# ? Jan 31, 2012 22:21

bairfanx: Jan 20, 2006; I look like this IRL,
but, you know,
more Greg Land-y.

So, I've got a problem that's looking like it's going to take a lot more time than I expected:

I've got monthly metrics reports for a department of ~15 people. There are ~20 different categories that each person reports their activity under.

Is ther a convenient way to produce individual, year-to-date reports for each team member?

Right now, since we're only in January, it would obviously be pretty simple, but ideally I'd like to be able to pull from up to 12 months worth of either separate sheets or workbooks and have each individual's ytd report show each month as a separate column.

Right now I'm frustratingly playing around with PivotTables, but despite being only moderately skilled with Excel, I'm pretty confident there's a better way than this.

# ? Feb 6, 2012 20:19

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

bairfanx posted:

So, I've got a problem that's looking like it's going to take a lot more time than I expected:

I've got monthly metrics reports for a department of ~15 people. There are ~20 different categories that each person reports their activity under.

Is ther a convenient way to produce individual, year-to-date reports for each team member?

Right now, since we're only in January, it would obviously be pretty simple, but ideally I'd like to be able to pull from up to 12 months worth of either separate sheets or workbooks and have each individual's ytd report show each month as a separate column.

Right now I'm frustratingly playing around with PivotTables, but despite being only moderately skilled with Excel, I'm pretty confident there's a better way than this.

For a small team like this you could make a master table on one tab with each team member and their stats. In column A have something like "Old James: 2012 01", "Old James: 2012 02", etc. Then create a blank template for the report layout. Copy the template onto a new sheet for each team member with their name in cell A1. Then have the fields where you want values use formulas like "=iferror(vlookup(A1&text(date(2012,1,1),": yyyy mm"),master_table,2,false),"")" to pull the individual stats from the master table.

It's not elegant, but gets the job done for a small team. A better option would be to use Access.

Old James fucked around with this message at 22:11 on Feb 6, 2012

# ? Feb 6, 2012 22:04

bairfanx: Jan 20, 2006; I look like this IRL,
but, you know,
more Greg Land-y.

Thanks! I feared there wouldn't be anything too elegant, but I'll give that a shot!

Old James posted:

It's not elegant, but gets the job done for a small team. A better option would be to use Access.

We were using Access before, and then orders from on high said we needed to use SharePoint for everything and right now what we've got going on in SharePoint seemd to only want to output to Excel. :smith:

edit: Physics major in a business job, I have literally no experience with creating things in Access, only using them. I've been playing with it a bit to see if I can't learn enough to make this both simpler and more aesthetically pleasing. Does anyone know of any good Access introductions?

bairfanx fucked around with this message at 20:47 on Feb 7, 2012

# ? Feb 7, 2012 20:21

rivetz: Sep 22, 2000; Soiled Meat

Still working on this pricing table (original summary of project) and learning a ton about Excel and enjoying wading through this stuff. Couple of (hopefully) straightforward questions:

I've opted to attempt doing this via defined names. There's a separate worksheet with a pricing table, and since Excel 2010 seems to automatically define names for the ranges in that table, I guess I'd like to just reference those defined names to obtain pricing.

My form takes user selections on bandwidth and term and populates the proposal worksheet thus:

Is it possible for the pricing to be done by code that references cell contents for a defined name lookup? For example, for E13 take whatever bandwidth has been populated from C13 and the term from E11 and just leverage the named ranges that Excel already has in place?

I know there are plenty of ways to pull that pricing and have tried Application.Vlookup, but these named ranges seem awfully convenient; this works as a formula (= _10M _24Months), but can VBA reference whatever those cells' values are in the current (active) row? If this is a really dumb way to approach, tell me. God, I hope this question makes sense; I'm even shittier at talking about this stuff than I am at doing it. For the record, they didn't hire me to do this or anything, I'm just working on it as a personal project to make my job easier.

rivetz fucked around with this message at 16:38 on Feb 10, 2012

# ? Feb 10, 2012 16:06

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 11:24

Dignity Van Houten: Jul 28, 2006; abcdefghijk
ELLAMENNO-P

I think this is an easy question. Say I have a workbook and on sheet1 I have a list of students.

Each row is a separate entry in no particular order.

Now say on sheet2 I have a overhead view of the classroom to make a seating chart.

Is there a way to have excel link a desk to one of the rows such that by clicking on a desk, that row in sheet1 is displayed in the highlighted region of sheet2? If not, is there a way to automatically add comments to each desk cell that reflect data from sheet1 (so that a quick mouseover of the desk will display the student and his/her information)?

Dignity Van Houten fucked around with this message at 23:49 on Feb 10, 2012

# ? Feb 10, 2012 23:46

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

«‹›82 »