Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
Adiabatic
Nov 18, 2007

What have you assholes done now?
Unfortunately I see the megathread has gone to archives.

As the new intern at an italian brake designer, and the second youngest employee at the particular facility, I have of course been drafted as the go-to guy for making poo poo in VBA in Excel since people automatically assume "I know about the computers". I've been able to handle all requests up until now with searching google and copy/pasting, then loving around till it somehow magically works. Until, of course, this morning.

I've been asked to, in the middle of a macro, pull up an AutoCAD drawing. I can handle the rest of the coding (I don't know if you could really call changing random poo poo and praying it works "coding", but whatever) by going back to previous projects... I just need to know how to open the draft using VBA code.

Thanks guys.

Adbot
ADBOT LOVES YOU

G-Dub
Dec 28, 2004

The Gonz
Do you work for Brembo?

Open it in AutoCAD I presume, or embedded in Excel somehow?

Google tells me you're not the only person with this problem: click here for a horrible website

G-Dub fucked around with this message at 20:46 on May 7, 2009

tef
May 30, 2004

-> some l-system crap ->
If you can run an external command, you can often get away with running "start filename.ext" to get the shell to open it.

i.e. Call Shell("start " & filename) (untested, I don't have access to windows machines)

Although for future reference, there are alternatives if you need a little more freedom:

Resolver One is a spreadsheet that uses python on .net internally:
http://en.wikipedia.org/wiki/Resolver_One

And if you're that way inclined, there are always libraries to interact with excel in other programming languages, even Haskell has one:

http://alfa.di.uminho.pt/~jacome/HaExcel.html

Adiabatic
Nov 18, 2007

What have you assholes done now?

G-Dub posted:

Do you work for Brembo?

Sure do, theres a plant and an R&D facility out in BFE Michigan.

Also thanks for the input guys, I'll let you know if I can bastardize it enough with my horrible excuse for code to get a halfway decent program running.

EDIT: Yeah trying to embed it in an Excel macro. The first link looked promising but of course Excel doesn't understand the "AutoCAD.AcadDocument". I honestly haven't a clue how to begin to handle the second post there, but I'll research the commands and see what I can come up with.

Adiabatic fucked around with this message at 13:07 on May 8, 2009

Zemyla
Aug 6, 2008

I'll take her off your hands. Pleasure doing business with you!

Adiabatic posted:

Yeah trying to embed it in an Excel macro. The first link looked promising but of course Excel doesn't understand the "AutoCAD.AcadDocument". I honestly haven't a clue how to begin to handle the second post there, but I'll research the commands and see what I can come up with.

You would either have to import it (I don't remember how right now) or create it with CreateObject("AutoCAD.AcadDocument"). The second would be assigned to an Object and you wouldn't have autocomplete, but it would work.

Swink
Apr 18, 2006
Left Side <--- Many Whelps
I want to know if its possible to select more than one item from a criteria when using the autofilter.

Example:
i have a list of fruit. I can use the autofilter to select only the apples, or only the oranges. But I want to see both apples AND oranges at the same time.

MattWPBS
Jun 17, 2004
I am the law, but an easily bribed kind of law.

Swink posted:

I want to know if its possible to select more than one item from a criteria when using the autofilter.

Example:
i have a list of fruit. I can use the autofilter to select only the apples, or only the oranges. But I want to see both apples AND oranges at the same time.

Yeah, go to "Custom" in the list. In the example, you set the first one to equals oranges, the radio button to "or", and select apples in the second.

DFWlly
Aug 6, 2004

by Fistgrrl
Ok...

I have a spreadsheet with an install date in column A. I have another date in column C (which represents the date of a service call). The dates in C are always after the date in A.

If an install date is 4/10/2006, then I need all dates in column C that are up to 7/10/2006 to be Quarter 1, from 7/11/2006 to 10/11/2006 to be Quarter 2, and so on.

Basically a quarterly calender year, but instead of Jan 1st being the start of a quarter, the install date in column A is representing the start of the year.

Edit: And to complicate things, is there a way to simply go Q1-Q20 instead of resetting to Q1 after the fourth quarter?

Eg.

Column A Column C Column D (this is the column I need represented!)
4/10/2006 4/11/2006 Qtr 1
4/10/2006 4/15/2006 Qtr 1
4/10/2006 8/20/2007 Qtr 7

EDIT: NEVERMIND!

=CEILING((DATEDIF(A1,C1,"m")+3)/3,1)

:smug: (ok another forum got it for me but still i'm feeling pretty drat good at 230am)

DFWlly fucked around with this message at 07:24 on Jun 12, 2009

ilikechapstick
Jun 17, 2007

very, very notorious.
Quick formula question that I can't figure out.

I have some data that looks something like this:

pre:
YEAR   
2009        TRO-01
2009        TRO-01
2009        TRO-01
2008        TRO-01
2006        TRO-01
2008        TRO-01
....

2009        TRO-02
2009        TRO-03
2009        TRO-04
2008        TRO-04
2006        TRO-04
2008        TRO-04
....etc,etc

All I am trying to do is get a COUNT on the number of records for say, a TRO-01 that happened in 2009 (this would be 2). I am trying to get a COUNT (but I need it with multiple criteria), and I don't understand array functions for some reason.

EDIT:
I've tried something like
code:
{=COUNT(IF((A2:A243="2009")*(C2:C243="TRO-01"),1))}
Year is A, TRO column is C.

ilikechapstick fucked around with this message at 20:14 on Jul 6, 2009

Beezle
Oct 19, 2008

Happy Steve Perry Day!

ilikechapstick posted:

Quick formula question that I can't figure out.

I have some data that looks something like this:

pre:
YEAR   
2009        TRO-01
2009        TRO-01
2009        TRO-01
2008        TRO-01
2006        TRO-01
2008        TRO-01
....

2009        TRO-02
2009        TRO-03
2009        TRO-04
2008        TRO-04
2006        TRO-04
2008        TRO-04
....etc,etc

All I am trying to do is get a COUNT on the number of records for say, a TRO-01 that happened in 2009 (this would be 2). I am trying to get a COUNT (but I need it with multiple criteria), and I don't understand array functions for some reason.

EDIT:
I've tried something like
code:
{=COUNT(IF((A2:A243="2009")*(C2:C243="TRO-01"),1))}
Year is A, TRO column is C.

This should do the trick:
code:
{=SUM((A2:A243="2009")*(C2:C243="TRO-01"))}
Bonus non-array version:
code:
=SUMPRODUCT((A2:A243="2009")*(C2:C243="TRO-01"))
Both assuming the year's stored as text as per your original function, otherwise lose the double quotes obviously :)

rekk1ess
Aug 21, 2005
I am reckless even when not drunk. Watch out!
I think this is a little problem but then again I am entirely not sure. I'll make a thread out of this if it is more complicated than I think.

Basically I have to clean up an Excel file with thousands of cells of data for a cell phone distributor. Here is my process:

1. In my Service Status Column I have to Filter and Delete invalid Statuses.
2. In the Sim Column I have to remove a Character from a string of numbers.
2. In my Service Number, IMEI Number, and SIM Number Column I have to replace blanks with a phrase, replace numbers greater or less than a certain value with a phrase, and replace all numbers that are duplicates with a phrase.

What I do is....
By Filtering the Service Status Column I can easily remove the invalid Statuses. And again by Filtering I can mark the Blanks.

I insert a separate column and I use the =LEFT(~,~) to output all characters I want from a SIM and not print the Character I would like to be excluded. Then I copy the Values over that and delete the old SIM Column.

I insert 3 separate Columns to check the length of my SIM, IEMI, and Service Numbers. Then I filter those that are good which allows me to Flag the improper ones.

I use Advance Filter to find the Unique Values of the SIM, IEMI, and Service Numbers and then Color them in a color to distinguish the Duplicates from the Unique. I filter what is not colored (the duplicates) and copy/paste them to another sheet. Then I use the Find and Replace on the copied/pasted numbers to replace all duplicates with the phrase I am suppose to.

This whole process takes me about 20-45 minutes to do depending on the size of the spreadsheet and the amount of duplicates that I have to find and replace.

My idea was to create a Macro to do the work for me. However, it is not working so well. I am going to retry it a couple of times but maybe you guys have a better idea for me. I have used the web to get me this far but I am struggling at finding a way to reduce my time in this lengthy process.

Any suggestions? I really suck at this. >_<


Edit:
I have been doing more research and figured out that as 1 Macro to complete all these tasks would crash. However, what is working is creating a Macro for each set of Steps and then having a Master Macro run the whole thing.

So far its going well. The code is basic but I am no expert. I'll keep you posted in case I have any questions.

rekk1ess fucked around with this message at 17:01 on Jul 11, 2009

Adolfo Castro
Aug 6, 2002
"I think rape is fucking hilarious."
This may seem like a stupid question, but how do I go about learning VBA in excel. I've been rigging macro's together by recording transofrmation and bastardising them with loops, but is there any way to get any sort of elegance out of the code? What's a good first step?

tef
May 30, 2004

-> some l-system crap ->
If it's possible to create elegant works of art out of poop, then I'm sure you can make better code in vba.

Learning the document model helps a lot -

I have read VB&VBA in a nutshell, which is pretty useful and flawed at the same time. A good reference when you know what to look for.

And I also have a copy of 'writing word macros'. I can't recall it being that useful.


In the end cargo culting VBA code together works pretty well for the trivial tasks it can cope with

rekk1ess
Aug 21, 2005
I am reckless even when not drunk. Watch out!
I am trying to edit my macro to allow me to fill in the Blank Cells, once filtered, with 05-MM-Blank. I am having issues though. I don't think that when it runs the first cell is blank. Anyway around this.


Sub Step_6A()
'
' Step_6A Macro
' Fill Service Number Blanks
'
ActiveCell.FormulaR1C1 = "05-MM-Blank"

Dim FirstRow As Long
FirstRow = [A1].End(xlDown).Row

Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row

Range(Cells(FristRow, 12), Cells(LastRow, 12)).Select
Selection.FillDown

End Sub


If it helps, this is what works:
Sub Step_6A()
'
' Step_6A Macro
' Fills Service Number Blanks
'
'
ActiveCell.FormulaR1C1 = "05-MM-Blank"
Range("L5", "L85").Select
Selection.FillDown
End Sub

However, L5 and L85 might change when I copy a new sheet into this. They might be L6 to L90 for example. I need this to adjust tot hat. Everything else seems to work fine.

rekk1ess fucked around with this message at 22:04 on Jul 13, 2009

rekk1ess
Aug 21, 2005
I am reckless even when not drunk. Watch out!
The following somewhat does what I want. Problem is, that it re-labels my 1st Column as 05-MM-Blank. Is there anyway to further this and Offset the FirstRow?

Sub Step_6A()
'
' Step_6A Macro
' Fills Service Number Blanks
'
'
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row
Dim FirstRow As Long
FirstRow = [L65536].End(xlUp).Row

Range(Cells(FirstRow, 12), Cells(LastRow, 12)).Select
ActiveCell.FormulaR1C1 = "05-MM-Blank"
Selection.FillDown
End Sub

tsingtao
Oct 29, 2004
Borderline creepy

rekk1ess posted:

The following somewhat does what I want. Problem is, that it re-labels my 1st Column as 05-MM-Blank. Is there anyway to further this and Offset the FirstRow?

Sub Step_6A()
'
' Step_6A Macro
' Fills Service Number Blanks
'
'
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row
Dim FirstRow As Long
FirstRow = [L65536].End(xlUp).Row

Range(Cells(FirstRow, 12), Cells(LastRow, 12)).Select
ActiveCell.FormulaR1C1 = "05-MM-Blank"
Selection.FillDown
End Sub

Not quite sure what you mean by offset the first row, but if you mean "refer to the row under FirstRow", you should try:

FirstRow = [L65536].End(xlUp).Row.Offset(1, 0)

That will return a reference to the row directly below what FirstRow is currently being set to.

Strong Sauce
Jul 2, 2003

You know I am not really your father.





Is there anyway to highlight a whole row based on whether or not a value in the row is in a list posted in a column?

For example if I have a list in the last Column that has Apple, Orange and Lime I want rows where the name of the fruit is in the row to be highlighted.

Edit: Figured out what I wanted to do with Conditional Formatting.

Strong Sauce fucked around with this message at 02:00 on Jul 30, 2009

tsingtao
Oct 29, 2004
Borderline creepy
Is this something you're doing through a macro, or what? If you're using a macro try something like:

code:
If WhateverCell.Value = "WhateverFruit" Then
     Whatevercell.EntireRow.Select
End If
Or if you want to set it up so that all rows containing a certain fruit are highlighted, try something like this:

code:
Do
     If WhateverCell.Value = "WhateverFruit" Then
          Union(Whatevercell.EntireRow, Selection).Select
     End If
     
     'Change WhateverCell to point to the next cell to be inspected
     WhateverCell = WhateverCell.Offset(1, 0)

Loop Until Len(Whatevercell.Value) = 0

gigabitnokie
Dec 2, 2008
I have a worksheet that looks like this:

code:
John    Smith     10/22/09
Jane    Doe       8/17/09
...
I'd like to have another worksheet display the names and expiration dates of those records whose expiration dates have already passed. Make sense? In this case only Jane Doe would appear on the next worksheet, but on 10/23/09 John Smith would appear as well.

Thanks in advance for any help ya'll can provide :)

Sub Par
Jul 18, 2001


Dinosaur Gum

gigabitnokie posted:

I have a worksheet that looks like this:

code:
John    Smith     10/22/09
Jane    Doe       8/17/09
...
I'd like to have another worksheet display the names and expiration dates of those records whose expiration dates have already passed. Make sense? In this case only Jane Doe would appear on the next worksheet, but on 10/23/09 John Smith would appear as well.

Thanks in advance for any help ya'll can provide :)

How automated would this need to be? Filter and copy/paste seems like the easiest, unless you're looking for an automated solution where every time you open the workbook the second sheet has only the "expired" info.

TheSpook
Aug 21, 2007
Spooky!
(I guess this is the new Megathread?)

I have multiple Excel files, each with a column of countries and some columns of data pertaining to those countries.

File 1:
code:
Country1   1.6
Country2   2.3
...
File 2:
code:
Country1   ABC   4.65
Country2   XYZ   3.21
...
I would like to merge them all into one Excel file, joining on country name. Is there an easy way to do this? I'm not really familiar with VBA.

rekk
Oct 9, 2004

TheSpook posted:


I would like to merge them all into one Excel file.

is it too complicated for copy & paste?

TheSpook
Aug 21, 2007
Spooky!

rekk posted:

is it too complicated for copy & paste?

The problem is this:

Some of the files have the countries listed in different orders, some are missing countries entirely.

I don't care about efficiency (it's only 45 files, so too much for a human but not much for a computer) -- my plan is this:

code:
Start some Master.xls
For each file f do
    Find the country column c
    For each row r in c
        If the country in r is in my Master's country list
             append the data to that row in the Master
        Else
             add the country to the Master
             append the data to that new row in Master
The problem is, I have no idea how to VBA :(. Working on it, but any suggestions are great.

Again, runtime = schmuntime for this.

Edit: I have it working for one file! Will post the code when finished. This seems like it'd be a very common problem.

TheSpook fucked around with this message at 04:01 on Sep 2, 2009

Sub Par
Jul 18, 2001


Dinosaur Gum
I am working on this while drinking a beer because it seems like a useful thing to have handy, I will be interested to see how you did it because I was making a lot of assumptions about your files (they would all have a header row, they wouldn't have duplicate data points, etc).

TheSpook
Aug 21, 2007
Spooky!
Thanks! We can certainly discuss.

Each of the files turned out to be significantly different, so I'm not using the outer "for each file f" loop. I'm opening each by hand and tweaking my code a little for each file.

The code itself copies all rows of N columns from the source to the destination, matching on some Key Column (in this case, the country name).

If no match is found, a new row (Country, {columns}) is appended to the destination file.

Edit: Here's a version: http://gannon-house.com/matching_module.txt. This is a mess of my own work with snippets from the Internet.

TheSpook fucked around with this message at 05:13 on Sep 2, 2009

MattWPBS
Jun 17, 2004
I am the law, but an easily bribed kind of law.
Here's what appears to be an annoying Excel 2007 bug.

Got a pivot that's created each time using VBA, so it's not a case of cached data that's not there any more.
Got a form where the combobox is populated using the pivotitems from the pivotfield, so it's not a case of the filter not actually being in the pivotfield.
Try and set the visible property of the pivotitem, and it throws a fit.

Basically, if you've got a pivotitem that's a UK format date (dd/mm/yyyy), it seems impossible to set a load of the properties. Throws up error 2042 instead. Can still get at the name, caption, etc, just not the properties I need.




Ooooooooooooooh, thinking about this, I wonder what would happen if I set the name to something else to start with, then tried to set the visible property?

MattWPBS
Jun 17, 2004
I am the law, but an easily bribed kind of law.

MattWPBS posted:

Ooooooooooooooh, thinking about this, I wonder what would happen if I set the name to something else to start with, then tried to set the visible property?

For anyone who runs into the problem, this works. Added "str" to the front of the name at the start, took it off at the end after setting the visible property.

Bloody 2007.

Dr. Video Games 0089
Apr 15, 2004

“Silent Blue - .random.”

I have a question when about exporting data from a website into excel. I'm still new to it so I'm not sure how I would be able to do this, I know I can click on the data tab > get external data > from the web and grab a table off a website but I need to get more complex than that.

I want to export data off a website and then have excel arrange the date/company/name/etc in an easy way so I can then upload it somewhere else.

For example, say I want to get the data off this website. I want Excel to take the Company Name, Person's name, address, city, state, zip, etc and upload it into each corresponding column.

In the end it should look like,

code:
Company     Name    Address       City       State     Zip       Phone #
Starbucks   Jack    1234 Street   USACity    PA        12359     23412341234
Starbucks   Jane    9876 Street   USAtown    CA        12359     23412341234
Starbucks
Coffee Store
Another Store
Java World
etc.
Any help is appreciated, thanks!

Edit - Actually, a directory like this is more of what I'm looking to export, http://www.directoriesusa.com/sampleReport.html. Since the data/address information is already organized with Address, City, Zip, etc, I imagine it would be easier to export it the way I want right?

Dr. Video Games 0089 fucked around with this message at 10:02 on Sep 16, 2009

smug forum asshole
Jan 15, 2005
I'm trying to make a Worksheet in Excel that pastes values from certain cells into successive rows of another worksheet, according to a Change event [1]. This is important because some people who are basically computer illiterate are going to be the primary users of the Workbook and I want it to be pretty easy for them to make order forms from a list of items sold by different vendors at different prices.

I've made little progress so far. I made a small amount of headway yesterday on the 'copying into another worksheet' part by using the name manager to define a name that had the values I was interested in. For some frustrating reason, this did not work at all when I tried to make the name definition column absolute instead of fully absolute (e.g., one cell in the defined name would be $A1 instead of $A$1). In this case, the same (incorrect) cells were called by the name no matter where on the spreadsheet I called the change event.

I've had no success on the 'into successive rows' part of the problem, and no idea how I might go about deletion. :(

[1] Basically I have a list of item names, prices, and quantities. I want to copy the name and price into another worksheet if the quantity is changed to something greater than 0, and removed from the worksheet if the quantity is zero or less.

smug forum asshole fucked around with this message at 20:57 on Sep 22, 2009

Just-In-Timeberlake
Aug 18, 2003
I'd just like to throw this out to anyone trying to figure out how to do something in Excel since at a job may years ago I wrote tons of Excel macros.

The easiest way to figure out how to code something in Excel is to start the macro recorder, do what you want, stop it and look at the code that gets generated. It will be overly verbose, but just find the part that is needed and adapt to your needs.

It'll save you tons of time searching documentation and the internet.

shotgunfilibuster
Jul 16, 2008
I have a problem that I feel like should have an obvious, simple solution, but am coming up short. I have a column with address information in the format of "[City] [State] [Zip]" and need to separate them into their own columns in order to export it from Access into ODBC. It would normally be a simple matter of delimiting the column by spaces, but the problem is that cities like San Francisco don't allow for something so easy.

Example:
code:
Albuquerque NM 87106
Schaumburg IL 60195
Santa Rosa CA 95402
Taos NM 87571
Franklinville NY 8322
South Lake Tahoe CA 96150
Moses Lake WA 98837
Cotzti CA 95401
Additionally not all the zip codes are in 5-digit format, some of them are the full 9-digit code. My thought is that I need to get the numbers out first, then it would just be a matter of using TRIM and RIGHT (I think?) to extract the state abbreviations. Any ideas?

Just-In-Timeberlake
Aug 18, 2003

shotgunfilibuster posted:

I have a problem that I feel like should have an obvious, simple solution, but am coming up short. I have a column with address information in the format of "[City] [State] [Zip]" and need to separate them into their own columns in order to export it from Access into ODBC. It would normally be a simple matter of delimiting the column by spaces, but the problem is that cities like San Francisco don't allow for something so easy.

Example:
code:
Albuquerque NM 87106
Schaumburg IL 60195
Santa Rosa CA 95402
Taos NM 87571
Franklinville NY 8322
South Lake Tahoe CA 96150
Moses Lake WA 98837
Cotzti CA 95401
Additionally not all the zip codes are in 5-digit format, some of them are the full 9-digit code. My thought is that I need to get the numbers out first, then it would just be a matter of using TRIM and RIGHT (I think?) to extract the state abbreviations. Any ideas?

There's probably a better way to do this, but as long as the zip is in ##### or #####-#### format (no spaces or this won't work), this works, it's quick and dirty, but it works:

code:
Sub ParseAddressFields()
    Dim i&
    Dim j&
    Dim zip$
    Dim state$
    Dim city$
    Dim str$
    
    For i = 1 To 8 'CHANGE THE 8 TO THE LAST ROW OF YOUR DATA
        str = Trim(Range("A" & i).Text)
                
        j = Len(str)
        
        Do While Mid(str, j, 1) <> " "
            zip = Mid(str, j)
            j = j - 1
            
        Loop
        
        str = Trim(Mid(str, 1, j))
        state = Trim(Mid(str, Len(str) - 1, 2))
        city = Trim(Mid(str, 1, Len(str) - 3))
    
        Range("C" & i).Value = city
        Range("D" & i).Value = state
        Range("E" & i).Value = zip
        
    Next i
    
End Sub

Just-In-Timeberlake fucked around with this message at 14:58 on Oct 1, 2009

shotgunfilibuster
Jul 16, 2008
gently caress yeah, that worked! Thanks! A minor bit of tweaking of my table and some clean-up after running the script and it did just what I wanted.

adante
Sep 18, 2003
Is there a way to apply string manipulation functions to array blocks?

I was trying to do this:

quote:

1 Dog asdf
2 cat asdf
3 Dog fdsa
4 dog bark
1 Dog bark

Sum the left column if the right column starts with "dog" in case insensitive.

I guess you can do this with multi-column retardation and (LOWER, ISERROR, FIND, IF) functions pretty trivially... is there a way to do this in a oneshot?

Incidentally it turns out you can do a case sensitive with SUMIF(B1:B5, "dog*", A1:A5) - where is criteria stuff like this documented?

Mark Kidd
Feb 15, 2006
If any of you excel folks would like to take a look at a macro/script project I'm working on, this may be your chance to pick up $15 in SA-Mart: http://forums.somethingawful.com/showthread.php?threadid=3221648

geetee
Feb 2, 2004

>;[

Mark Kidd posted:

If any of you excel folks would like to take a look at a macro/script project I'm working on, this may be your chance to pick up $15 in SA-Mart: http://forums.somethingawful.com/showthread.php?threadid=3221648

Ha ha, I remember you. I sent you a quote once for an online ticket booking system. You never responded after the quote and it all makes perfect sense now. You'll probably shat your pants at the quote. I can't believe you're finding suckers to do your day job for so cheap.

Mark Kidd
Feb 15, 2006

geetee posted:

Ha ha, I remember you. I sent you a quote once for an online ticket booking system. You never responded after the quote and it all makes perfect sense now. You'll probably shat your pants at the quote. I can't believe you're finding suckers to do your day job for so cheap.

Howdy, yeah that was more than we had on hand to spend, so I wound up sticking with a much shallower integration with our ticketing service and am keeping an eye out for future opportunities to build that out a bit more. I did send you a note to that effect though, sorry to hear that you didn't get it.

Unfortunately it's not really anyone's day job in my office to handle this kind of work so sometimes it does become my responsibility to hunt up someone who has expertise we otherwise don't. In my line of work, making an airtight case for a new expense can just mean that we have what we need to begin talking to our donors and adding it to proposal budgets so as to try to find funding.

One of our accomplishments in 2009 was funding, finding, and retaining IT consultants. Our organization is working carefully for 2010 to raise funds to build relationships with database and web developers, two other professions I have had to dole out work to piecemeal. So, one step at a time.

me your dad
Jul 25, 2006

I have a spreadsheet with the following bit of data:



The user of the spreadsheet will enter data under the Actual column for Calls, Sales, and Hours. CPH and SPH are just Calls Per Hour and Sales Per Hour. This part is already figured out.

What I need:

The third column is Year To Date. These cells should reflect the number entered into the Actual column. This is of course, easy enough.

However, each week, this worksheet will be duplicated and the YTD column will need to be a running total based on the previous week. Each week the data in the Actual column will change.

Can someone help me out in getting a formula together for this? I'd rather not do it in VBA but I can if necessary.

Little_Yellow_Duck
Feb 16, 2002
Who wears short shorts
Is there a simple excel function to evaluate a text string as a function? Here's a possible example sheet:
pre:
Sheet To Evaluate     Cell To Evaluate   Result
Sheet2                A1                 =EvalStr("='" & T(A2) & "'!" & T(B2))
Sheet7                D4                 =EvalStr("='" & T(A3) & "'!" & T(B3))
so the Result column would return the same as if it were filled with ='Sheet2'!A1 , ='Sheet7'!D4 .

Edit: Looks like I'll have to read up on VB Script. From this pcreview forum:
code:
Function myFunct(InputString)
Application.Volatile
myFunct = Application.Evaluate("=" & InputString)
End Function

Little_Yellow_Duck fucked around with this message at 20:35 on Nov 23, 2009

Adbot
ADBOT LOVES YOU

TheAngryDrunk
Jan 31, 2003

"I don't know why I know that; I took four years of Spanish."

Chinaski posted:


Can someone help me out in getting a formula together for this? I'd rather not do it in VBA but I can if necessary.

My first inclination would be to talk you out of doing it on multiple sheets.

Failing that, however, you can sum cells across worksheets. For example, this formula =SUM(Sheet1:Sheet3!A1) will sum all of the A1 cells on the worksheets that are in between Sheet1 and Sheet3 (inclusive).

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply