Ask me anything about Microsoft Excel - Page 7 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

exec771 · 09-13-2010, 02:20 PM

Quote:

Originally Posted by zomg

Stupid things American girls have said to me would probably be worthy of it's own thread, e.g.
- Are you from [country that is no where near australia]? e.g. England, South Africa, New Zealand, Ireland

wat

aces_dad · 09-13-2010, 02:53 PM

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?

**killa** · 09-13-2010, 03:14 PM

Quote:

Originally Posted by Tony Lepatata

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

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

magent · 09-13-2010, 03:35 PM

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.

Dudd · 09-13-2010, 03:39 PM

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.

FC Juggernaut · 09-13-2010, 04:54 PM

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.

zomg · 09-13-2010, 05:39 PM

Quote:

Originally Posted by PolvoPelusa

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.

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

Quote:

Originally Posted by exec771

wat

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

Quote:

Originally Posted by aces_dad

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?

If the hyperlink is in A1 then you can do:

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

Quote:

Originally Posted by magent

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.

Quote:

Originally Posted by Dudd

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.

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

Quote:

Originally Posted by FC Juggernaut

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.

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

KanMan · 09-13-2010, 06:10 PM

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?

growingskin · 09-13-2010, 09:05 PM

Quote:

Originally Posted by zomg

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"

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

sockhead2 · 09-13-2010, 09:32 PM

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!

CopTHIS · 09-14-2010, 04:34 AM

Quote:

Originally Posted by KanMan

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?

I'm sure there are but everyone has tended to learn on the job in my team - the thing is, once you know how formula work and how to look them up you can usually find out what you need. Most firms will have their own functions built it (eg mortality tables) too.

zomg · 09-14-2010, 10:11 AM

Quote:

Originally Posted by growingskin

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

Format > Conditional Formatiting
Formula Is.. =A1="Paid"

i thought you could do it without specifically referencing the cell but i just checked and it doesnt work

Quote:

Originally Posted by sockhead2

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

Cool, feel free to answer some questions.

Quote:

Originally Posted by CopTHIS

I'm sure there are but everyone has tended to learn on the job in my team - the thing is, once you know how formula work and how to look them up you can usually find out what you need. Most firms will have their own functions built it (eg mortality tables) too.

+1 to never reading an excel book you will probably just be wasting your time

zomg · 09-14-2010, 10:30 AM

From PM:

Quote:

I was wondering if you could tell me a little bit about how you made that AFL draft picker, not so much the programming side of things but how you collected the data and how much weighting you placed on various aspects of a players performance.

The fantasy site was www.ultimatefooty.com, they have stats for the last few years on goals, kicks, handballs, marks, tackles

1. Import results into excel

Code:

For i = 0 To 19
    Application.StatusBar = i
    tempURL = "http://ultimatefooty.com/" & leagueID & "/players?status=ALL&pos=P&club=ALL&stats=2009_AS_A&sort=rank&count=" & i * 25
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & tempURL, Destination _
        :=Range("$A$" & Range("A65000").End(xlUp).Row + 1))
        .Name = "players?count=" & 25 * i & "&sort=drank"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next i

2. ran a macro to randomise a draft with 12 players then worked out what the average weekly score would need to be to win in each category (i checked at the end of the season and it was almost exact)

3. as the draft was going, kept track of the two stats that were the biggest % away from average and looked at all available players weighted by % behind e.g.
cat1pct = tmpcat1diff / (tmpcat1diff + tmpcat2diff)
cat2pct = tmpcat2diff / (tmpcat1diff + tmpcat2diff)

then id rank the two categories added together (multiplied by each weighting) and the lower score is the best player available in those two stats

"=RANK(" & col1 & "2,$" & col1 & "$2:$" & col1 & "$600)*" & cat1pct & "+RANK(" & col2 & "2,$" & col2 & "$2:$" & col2 & "$600)*" & cat2pct

defixated · 09-14-2010, 05:33 PM

Excel is Microsoft's killer app and is substantially responsible for the success of Windows. Joel Spolsky, who was one of the early project managers (now sort of a software guru), says that by far the most common use for Excel is manipulating lists. So while all the programmers want to replace Excel with an RDBMS (even one as miserable as Access), this is mostly missing the point.

Btw, if you are just learning to code, you will be amazed at how much time and hair-pulling date arithmetic requires. For a while I tried to get everyone to store dates as integers formatted YYYYMMDD.

Also, cool story bro re: obscure date functions, young Spolsky and Bill Gates: http://www.inc.com/magazine/20080701...lory-days.html

DeezNuts · 09-14-2010, 09:23 PM

why do array tables kill my cpu?