The job i got off craigslist i would say was just lucky, I moved here in december last year and travelled the states on and off for about 6 months, spending my savings and not doing much work at all. Then one day i saw an ad for access/excel vba and i sent them an email the next day i made my only visit to their office and i have been working for them since. Every other contract I have, i worked for them at one stage (except my latest one which was through a friend)

This job also started at $30 p/h for the first month because they wanted me to do work in access/mssql/php/mysql which were all languages i had never used before. It has since gone up to $50 and is still my lowest rate but they give me the most work so i'm not complaining.

It seems like you have a lot of other skills, and I would suggest maybe learning some PHP and getting into contract web design, theres lots of work out there for a good php/mysql programmer. PHP is an awesome language i had experience with HTML/CSS/Javascript and was fairly competent in a month. The best thing about this industry is i never get stuck doing the same thing for too long, there's always another language to learn or a different project to do.

I understand people who program 9-5 for the security but I can think of nothing more tedious than working on the same program for years at a time. I'm fortunate that i'm still young and selfish and don't have anyone else relying on me to make money, it's good while it lasts.

As an aside this attitude does create an interesting dynamic with my 'clients' who all know that work is far from my main priority and that i'm not in it for the long haul. In my interview with the company from craigslist i flat out told them that while I was happy to do the occasional all nighter, I didn't come to NYC to work 12 hours days and I plan on travelling a lot.

Part of the reason i moved was that i wasn't ready to get a 9-5 job back home because I didn't know where i wanted to be. I think the best use for my skills would be external to a company if only because i get bored easily and i wouldn't last at a single company long enough to move up the ladder. My hate for the corporate ladder is another topic

Yes, i just looked at the code and it won't actually work, you can only use range(var & "1" : var2 & "2") if var2 is a letter (you could use ASC() ) and convert it to a letter but it's not a good way.

This is the only time i use the .cells command

e.g.

range(cells(1,var1),cells(2,var2))

hope that makes sense

edit: if var1 and var2 are letters then the correct syntax would be:

range(var1 & "1:" & var2 & "2")

note that you need the colon inside the quotes

Templates used company wide, write a macro to go through every file in a folder and aggregate in a spreadsheet, i mentioned this before but most companies don't want to use access and are happy living their lives in excel. A lot of the time I will be doing things where the company has an existing process with a designated input and output that they are happy with. For example a movie studio had a list of all DVD release dates in a master file with data spread across 4 different tabs, when ever they wanted to make a monthly one someone would spend hours copy and pasting from all the different sheets and creating a monthly workbook. The workbook design was already decided on and everyone was happy with it, all i did was automate the creation of it.

Sure it might be more efficient to move all the DVD releases to a huge database but that would require a huge change which no one wants to pay for.

This is especially true with anyone in the finance industry who would never leave excel if they didnt have to.

edit: i see copThis covered this, this is the attitude of every company i have worked with

This

Sure this makes sense - and its a good reason to use Excel. But once you get to the point where you have a job that is too complicated to do in-house and you get an outside contractor to work on the problem it seems strange that you still get a basic product.

Fair enough. I would still think its better to take the raw data, import it into Access, and then show people how they can do all sorts of stuff (ad-hoc querying, create standard reports, what have you). I mean we're talking about at most a couple of days of work and a super easy product to work with. It becomes much easier to use.

So you spend a lot of time doing really small jobs like this? Each of your examples seems like only a couple of hours of work. Are you just contracted as this companies Excel dude and given random tasks?

I want to make my row change it's color (green) automatically when I enter a text in one column of the row like "paid", how do you do that?

conditional formatting

who are these people you speak of that can be taught the things you list?

99% of my audience require their data to be spoon fed to them. I've realized that most people think that being able to put some numbers in a few cells of an excel spreadsheet means they know how to use excel...when in reality, it just means they can put some numbers in a few cells of an excel spreadsheet.

I'd rather bleed from my ears than waste my time trying to explain to them how to query data from a relation data set.

Okay, here is a logistical problem that I do not know if Excel can handle.

I have this algorithm based on the Colley's Matrix Rating System used in the BCS for college football. Basically, you set up a matrix and solve an eigenvalue equation. Here is my spreadsheet that I created implementing the algorithm, and it works fine for storing my matrix and calculating some trivial things.

Here is my file: matrixratings.xlsx

My question, is there a way to implement an automatic counter so all I have to do is input a winner and a loser (the opponent of the winner) and it will a) automatically decrement the two intersecting rows and columns and b) update the win/lose columns appropriately? Since I am human, there are times where I mess up and need to re-enter things from a previous save.

Also,is there a way to solve the eigenvalue equation (to the appropriate precision) inside of Excel? Right now I have to copy and paste the matrix values into a new page (The CVS page), export the page as a csv file, import into Mathematica, type in the appropriate commands and paste in the values into Excel.

The answer to both of these are pretty similar in that while I was at university i worked part time for three different companies as part of my degree. While working there I did excel development for them on the side in my spare time. Through those contacts i got work doing small-ish jobs (usually 20-40 hours at a time).

In addition during my last year of university i worked at a huge fund management firm 3 days a week, they had about 1000 different spreadsheets used nationwide and i basically worked with every level of management trimming the fat and automating everything. They still have small things that they need and e-mail me occasionally.

My main source of income at the moment is for an american company that i do 20-40 hours p/w depending on what's going on in my life.

The only contract i have that falls outside of this is one my friend got me because he knows what i can do in excel and he saw where it could be used so he approached management, they asked for a sample that i did in a few hours for free and now have about 50 hours work lined up over the next 3 months at almost double what i'm getting paid in NY

this. It's either trying to teach a whole company how to use a brand new database software or writing a macro to compile data at the end. A lot of my work is with the final user of the data when they dont have the funding/time to go through there main IT department and can't change the input. whacking a database on at the end is just asking to be not used as soon as i walk out the door.

In 2007: Click on the cell, go to the home tab on the ribbon, select conditional formatting > 'equal to' then in the box that comes up type "paid" (without quotes)

In 2003: from memory i think its data > conditional formatting then go to formula and enter ="Paid"

Um... that's why you create reports for them. Access has lots of ways to view the data that requires no knowledge of databases. Other DBs have similar free reporting software.

My point is just that if people are this stupid it seems useful to make the data even simper than an excel spreadsheet.

tbh the whole spreadsheet looks prone to errors, lets say Aves beats Brian then what value do you put in C6 and F3? how do you know who beat who? do you put a -1 then manually update the win/loss columns?

Even in the companies that have databases, they all want their reports in excel because that's what everyone is used to. No one sees the value in retraining a few people on access for benefits that they can't see. I already said a few times I agree that using databases is the better solution most companies just don't care.

Yes.

Hence, why I'm asking if there is an easier way.

I added another sheet called data then in column A i put the winner and column B the loser in the matrix cell B2 put the formula

=IF(B$1=$A2,$AG2+2,(SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*1)+SUM((data!$A$2:$A$1000=B$ 1)*(data!$B$2:$B$1000=$A2)*1)))

instead of pressing enter press ctrl+shift+enter (this does an array formula) then copy and paste the formula in B3:B29 then copy and paste B2:B29 to C2:AC2 (you cant copy and paste over an existing array formula so you have to paste around if that makes sense

this will complete the grid then in the win column put

=COUNTIF(data!A:A,A2)

and for losses put

=COUNTIF(data!B:B,A2)

I realise you will have to go back and add in the games but its probably better having them all written down so you can go back and verify results

Cool, thanks. I'll try it. Is there a way to solve the eigenvalue equation inside Excel?

We actually never use contractors - either we do whatever is needed ourselves, or use specific software that has been developed for that purpose. (There is really one one job that we use that software for.)

The thing about Excel is that you can basically use it as a calculator - in fact. some people in the team (me included) don't have a calculator at work even though we fiddle about with numbers all the time. It's so much easier in Excel as you can break the formula down, see intermediate steps etc. Most jobs are simple-ish and one offs, rather than projects so Excel is perfect. That said, there are some jobs that we've used Excel for that would have been better in Access.

simpler than an excel spreadsheet? example?

also, you'd be surprised how many people want to see all the "fancy colors". "Can we do that red, yellow, green thingy you did for the XYZ reporting?" What is simpler than excel and allows you to do "green thingys"?

This was really just my point. It seems strange to me that there's a demand for an Excel contractor since this is the attitude I would expect from most companies.

I mean obviously there's some demand since OP is making a living at it but I'm still surprised that companies have enough little Excel jobs to make it worth their while.

Edit: I guess I also feel like there's probably room to upsell a company like that to a better solution. If a company is constantly investing in Excel work week after week I have a hard time believing that Excel is the right solution.

I've never done is specifically, but there appears to be a lot of resources available:

http://controls.engin.umich.edu/wiki...icrosoft_Excel

http://lertap.curtin.edu.au/HTMLHelp...igenvalues.htm

http://people.revoledu.com/kardi/tut...igenValue.html

I only glossed over them because i've never calculated an eigenvalue before but it does seem possible

I did mention that i do spend a fair amount of time convincing companies to not use excel for certain processes

I have been trying to take pdf files of past performances from DRF.com and move then into excel, but the files off of DRF.com are password protected. Is there anyway around the password?

what is a pivot table?

what is it's functionality?

Firstly, I know i've removed passwords from PDF fles before (i found some program on google) but never in excel and it would probably be a long process.

In saying that I just checked the site and while its hard to say without a log-in it seems like you can view the results in .PDF or just on the website, if you can get it to display on a website you can import it into excel using VBA. Click on the data tab (in 2007) and go to "import from web" then log-in and navigate to the table you want. If it works and you record it in the macro recorder you should be able to write some VBA to go around it to do it automatically.

It instantly creates a summary of large data sets so if you have a table with 10 columns and 50,000 rows you can create a pivot table on another sheet and it will automatically aggregate the data. It can be created via the Insert tab in 2007 then it will give you options of which column headings to show on the pivot table and you can drag and drop and move things around.

It's a very fast way to get totals/counts of rows and i think it is also good for quickly sorting