thats not the point dont start an ask me thread about "using MS Excel" and not know how to use a part of it

I'm letting it slide but understand that i scrutinize computer type ask me threads far more then most since its my industry

I knew when I made it there would be extra scrutiny because i'm sure there are a disproportionate amount of IT people on this forum. I should have titled it "ask me anything about excel automation" because that's more where my speciality lies.

In saying that, i was hoping there are finance people here who spend their whole day in pivot tables and finance functions that i will never touch who could benefit from automation and can help others with pivot tables questions.

fwiw I work at Hedge Fund as there IT so i know a few things about thsi
but carry on carry on

The nerd turf war that is going on in here is ruining this thread for people who don't give a **** which of you guys know more about Excel.

that is the problem. when you try to import from web, then a box pops open asking you for a password at that point. I did not know if there is another way for the data to be moved from pdf to excel

is that why your name is 'killa'?

Cool. My matrix isn't symmetric, so I'll keep the Mathematica step. But your formula worked perfectly (aside from the -1, but that was simple). I didn't know about the function "SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*1)" and that was what I really wanted. Thanks.

If you log in, in the excel web browser once you shouldn't have to do it again. Worst case scenario if you manually run the import in excel then you could run a macro to instantly clean it up

I'm busy at the moment but later tonight i'll create a log-in and see what i can do, what specific information do you need from the site? Is it results from every track on every date for every race or do you just want specific ones that you pick?

I was going to mention it in the OP as my favourite formula

it's basically a countif on two or more columns of data the *1 at the end makes it a countiF if you changed the *1 to another range it would act as a sumif, for example if you had points scores in column C of data then you could get the total points scored for games when A2 beat B1 by using

SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*(d ata!$C$2:$C$1000))

something like that, anyway put the thread back on track as I said

I have a model for picking horses. Currently, I have to do all the calculations by hand. but if I could import all the data to excel, and then write some simple macros, you can imagine how many more races I could handicap and how much faster it would go.

good thing you're not in their grammar and spelling department

Not sure if excel has it, but in access you can add a web browser active x control to a form which has all sorts of built-in functions and triggered events. So basically you could load the site into the browser component, toss in your uid/pw, iterate through the PDF links (parsing the HTML), download the files to a specific directory, switch over to excel and pull them into a worksheet. From there its as easy as writing a macro to process the data to suite you needs.


The browser control was added to the .NET framework in v3 or 3.5, but I never realized there was a control in access. Would have saved me a lot of time for some screen-scraping apps I've put together in the past.

wat

I have a cell which has a hyperlink which looks like this:

http://myCompany/sap(bD1lbiZjPTkxMA=...?id=9000416915

I want to update the last 10 numbers in the hyperlink and have the 'Text to display' automatically reflect this:

9*416915

I'm finding myself having to edit both the hyperlink and the Text to display field. Can you provide a way to edit only one of these two fields and have both updated?

making fun of my spelling and grammar? How ****ing original of you

Say I have permutation of units that I have in order in column A. I'll use the alphabet to simplify and illustrate.

AA
AB
AC
AD etc. to AZ, then

BA
BB
BC
BD
BE etc.

CA
CB
CC
CD

and so on till ZZ. Each two letters are in a cell, and now I'd like to completely randomize the worksheet, so that the cells in the column would read like:

XA
CV
YH
LB

and on. How do I do this? Doesn't seem to be any 'randomize cells' button or anything, but I've been told it can be done.

One really easy way to do that is to create a column of random numbers and then just sort by that column, then delete the random column when you're finished sorting.

What do the little green triangles mean in the top left corners of cells? i feel like they are responsible for not letting me perform simple sum equations for columns of numbers.

Never used it before but i imagine its the same thing as the one in excel, i think he said that the PDF was password protected and he couldnt copy data out, which is why i was looking at importing the whole page and formatting with vba

I think the accent is so different to an australian accent that i'm not correcting myself!

If the hyperlink is in A1 then you can do:

=HYPERLINK(A1,RIGHT(A1,10))

more explanation if you need: in column C put =RAND() then copy down, then sort by column C and it will be random, if you want to re-randomise then go to the formula tab and click calculate sheet and sort again


They are just notes from excel, off the top of my head you get them if you are displaying a number as text and also if you have 10 cells in a row with the same formula (or a formula copied down) then you change one in the middle it will alert you that there is an inconsistent formula

personally i usually just right click on the little green thing then hit ignore

CopThis/zomg/anyone else,

I'm entering into the actuary field and I need to touch up on my excel. I never took any classes and my only experience with excel were at my job, so i'd say im pretty much a beginner.

Are there any good books out there I can pick up to get acclimated?

thanks for your reply

But I can't seem to get it to work on 2003, I haven't tried it on my 2nd computer with 2007, I'll have to try it later although I really wanted it to work on 2003 > this my main pc

I can do anything in Excel, but what I've found over and over to be a big problem is people insisting on using Excel, when what they really want is a database. Great, I can figure out how to do all kinds of manipulation, apply all kinds of filters and sorting, and using Excel 2007, do all this on a couple hundred thousand rows of data... and because I'm really clever and a total Excel guru, I can make it work without crashing the computer, and I can make it run in under 2 hours. Or, if we dump it into any database -- even MS Access which everyone's got on their computer "for free" in any work environment using Excel -- anyone with even basic database skills can do everything I did in Excel, and it will only take 5 seconds to run. And there is no risk that it will crash anyone's computer.

Ug.

Know what I mean? So while I'm totally impressed with everything Excel can do, and I find it incredibly fun to tackle huge and seemingly impossible tasks in it, I'd much rather people just accept it for what it is and use other programs when it is more appropriate.

Anyway, one other thing. I can do anything in Excel, including use pivot tables. Nanner nanner boo boo!