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

Thanks for your help. I honestly didn't think this was possible. I'm afraid I still haven't been able to make this work. Is there something I need to do to activate this code or something?

If you are using 2007 then you need to save is as a .XLSM and when you open it you might need to click "enable macros" in the bar when it opens up (it says 'some content has been blocked' just below the ribbon)

Then just enter a number in A1 (e.g. 1000.4355436) then in A2 put a 1,2,3,4,5,6,7,8 whatever oyu want and it will format A1

hope that makes sense

Quote

12-19-2010 , 10:21 PM

#277

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Str8Fish

I use excel macros extensively at work. I've been working with files that are runlogs, taking data every second. I work with many of these files and want to do many comparisons of different runlogs. I'm starting to think that it would be better if I move my stuff to access... but don't think access is really designed for runlog type data, more listed data. The reason is that I compare maybe 20 runlogs together and the excel sheet becomes hundreds of graphs and calculation sheets after it goes through my macro.

Question is, do you agree that I should explore options in access? Have you ever heard of anyone using runlog data in access successfully?

It should be possible in theory my only issue would be that every week you are going to be adding 144,000 rows (8 hours days, 5 days a week) so it's going to be unworkable after a couple of weeks (access is pretty bad for massive databases - have you tried looking at a more stable database platform?)

If your main use is graphs & calculations then you are going to need to import it back into excel either way, maybe you could make some sort of weekly summary and import that into access. Hard to say without seeing the exact data. Can you post some of the macros? maybe you can optimise them further?

Quote

12-19-2010 , 10:26 PM

#278

Str8Fish

veteran

Join Date: Jun 2005 Posts: 3,234

nah it's not every second of every day. A typical run is maybe 30 min long, so it's every second for 30 min, but then we'll have maybe 20 runs in total.

Quote

01-03-2011 , 07:17 PM

#279

Dasq1306

adept

Join Date: Jul 2008 Posts: 955

Hey just a quick q : I know how to link pdf/word files to specific excel files but is it possible to keep that link when sending both the pdf/word file and the excel file to someone else ? If it can't be done directly, can it be done at all ?

Quote

01-08-2011 , 12:09 PM

#280

xander biscuits

watching a movie

Join Date: Oct 2009 Posts: 39,745

I have excel 2003

how can I create a 6 degrees of seperation thing with it?

Quote

01-08-2011 , 12:36 PM

#281

kerowo

lolcat

Join Date: Nov 2005 Posts: 37,190

You know, just use a function.

Quote

01-09-2011 , 02:15 PM

#282

Sully

adept

Join Date: Apr 2004 Posts: 707

I am pretty good with the basics of excel, but reading this thread makes me realize I know next to nothing.

Is it possible to create a template with preset formulas so that I can import data and have it automatically sorted and calculated?

Example: When analyzing home values, I import neighborhood data directly from the MLS into excel. There is an incredible amount of data (probably 100-150 columns), of which about 30 columns are relevant.

Typically, I manually delete all the columns I don't need, then create formulas to calculate averages, etc. This takes forever. I am curious if there is a way I can preset all the formulas so that I can just import the data and have it automatically analyzed, instead of re-creating it every time.

Quote

01-10-2011 , 03:56 AM

#283

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by Sully

Pivot table? Just hide all the columns you dont need and put it on average (I might have misunderstood your question).

Quote

01-10-2011 , 01:17 PM

#284

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Sully

How do you import from MLS to Excel? If you use the macro recorder and record everything you do it shouldn't be hard to combine the recorded macro with an VBA based import, something like:

Code:

Sub sortAndCalculate()

    sFileName = Application.GetOpenFilename
    If sFileName = "False" Then Exit Sub
    
    Workbooks.Open Filename:=sFileName
    Set tempWB = ActiveWorkbook
    Set tempWS = tempWB.Sheets(1)

    With tempWS
         ' recorded code here
    End With
End Sub

or if you wanted to keep it all in one workbook you could do:

Code:

Sub sortAndCalculate()

    sFileName = Application.GetOpenFilename
    If sFileName = "False" Then Exit Sub
    
    Workbooks.Open Filename:=sFileName
    Set tempWB = ActiveWorkbook
    Set tempWS = tempWB.Sheets(1)
    
    set curWB = thisWorkbook
    set curWS = curWB.sheets("Template")

    tempWS.columns("A:A").copy destination:=curWS.range("A1")
    tempWS.columns("C:C").copy destination:=curWS.range("B1")

End Sub

hope that helps!

Quote

01-10-2011 , 01:18 PM

#285

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by xander biscuits

I have excel 2003

how can I create a 6 degrees of seperation thing with it?

Not sure if this a serious question, but you might need to elaborate a bit if you want a proper answer

Quote

01-10-2011 , 01:25 PM

#286

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Dasq1306

I'm pretty sure everything after excel 97 automatically uses relative hyperlinks and should take care of this.

Does it not work for you? What happens?

Quote

01-16-2011 , 03:55 PM

#287

g-bebe

because robots are strong

Join Date: Feb 2007 Posts: 22,860

I'm making a spreadsheet and want to use coloured backgrounds for individual cells to make things easier to read. Only using two colours: green (right) and red (wrong) obv. Is there a way in Excel to calc how many cells ina given column/row are green (or red)?

Quote

01-16-2011 , 04:19 PM

#288

Dasq1306

adept

Join Date: Jul 2008 Posts: 955

Quote:

Originally Posted by zomg

I'm pretty sure everything after excel 97 automatically uses relative hyperlinks and should take care of this.

Does it not work for you? What happens?

Hey tx for answering. I just hyperlink a word from the Word file to a cell of the excel file and it doesn't work anymore when i send it. Im a complete newb though so could you tell me how you would go about it ?

Quote

01-16-2011 , 10:34 PM

#289

Cardfish1

veteran

Join Date: Apr 2009 Posts: 2,018

I have a spreadsheet that I type in a lot website addresses (e.g. www.xyz.com). Excel turns them into hyperlinks and changes the font size. I don't want them to be hyperlinks or have the font size changed so every time I have to type in web address, hit enter, right click the cell, remove hyperlink, right click cell again, change font size back.

Is there any way to stop excel from automatically making the website address into a hyperlink?

Quote

01-17-2011 , 01:34 AM

#290

Cueballmania

Carpal \'Tunnel

Join Date: Sep 2005 Posts: 14,165

I'm sure you can change it, but when it does it, you can hit "ctrl + z" to undo the last action (even an auto action) and it will remove it. It's a universal MSOffice trick when it formats poorly.

Quote

01-17-2011 , 03:59 AM

#291

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by Cardfish1

Instead of hitting enter, just use the down (is that the name?) key on the keyboard, it should work.

Quote

01-17-2011 , 05:01 AM

#292

ret44

newbie

Join Date: Aug 2009 Posts: 16

The other thing you can do to remove hyperlinks involves VBA - but it'll do a whole batch at once.

Press Alt+F11 to bring up the VBA editor.

Press Ctrl+G to bring up the "Immediate" window

Into that window, type:

activesheet.hyperlinks.delete

This will take care of any and all hyperlinks on a sheet.

Quote

01-17-2011 , 07:21 PM

#293

Cardfish1

veteran

Join Date: Apr 2009 Posts: 2,018

Quote:

Originally Posted by Cueballmania

I'm sure you can change it, but when it does it, you can hit "ctrl + z" to undo the last action (even an auto action) and it will remove it. It's a universal MSOffice trick when it formats poorly.

That works but it's not quite what I'm looking for.

Quote:

Originally Posted by Spurious

Instead of hitting enter, just use the down (is that the name?) key on the keyboard, it should work.

This didn't work.

Quote:

Originally Posted by ret44

I typed that in but it didn't do anything. Am I supposed to save it or just close the page out after typing it? I have no clue on the VBA stuff.

Quote

01-17-2011 , 11:06 PM

#294

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Cardfish1

What version of excel are you using?

in 2002+:
1. Tools > Autocorrect options
2. Go to "Autoformat as you type" tab and uncheck the hyperlinks one

In 2007+:
1. Right click on the ribbon where it says "home/insert/page layout etc etc"
2. "Customize quick access toolbar"
3. On the far left go to "Proofing" > Autocorrect options
4. Go to "Autoformat as you type" tab and uncheck the hyperlinks one

Quote

01-17-2011 , 11:24 PM

#295

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by g-bebe

You can use a UDF (user-defined function)

1. Right click on a sheet tab > view code
2. On the far left, right click on VBA Project > Insert > Module
3. Paste the following

Code:

Public Function countInteriors(currentRange As Range, interiorColor As Integer) As Integer
    Dim curCount As Integer
    Dim ce As Range
    
    curCount = 0
    For Each ce In currentRange
        If ce.Interior.ColorIndex = interiorColor Then
            curCount = curCount + 1
        End If
    Next ce
    countInteriors = curCount
End Function

Public Function colorIndex(currentCell As Range) As Integer
    colorIndex = currentCell.Interior.colorIndex
End Function

4. Go back to your worksheet and then you can do =countInteriors(A1:A100,3)
5. Where A1:A100 is the range and 3 in the color index
6. to find the colorindex you can use the other function =colorIndex(A1)

Hope there's enough their for you to use

Quote

01-18-2011 , 07:11 AM

#296

Cardfish1

veteran

Join Date: Apr 2009 Posts: 2,018

Quote:

Originally Posted by zomg

I have 2007. This worked awesome. Thanks

Quote

01-18-2011 , 09:49 AM

#297

work_lol

grinder

Join Date: Oct 2007 Posts: 493

When I try to enter like 16 digits in a cell, it automatically changes to scientific notation. How can I be able to enter a bunch of digits and not have it changed when I move on to the next cell? I tried everything in format cells and no help.

Quote

01-18-2011 , 10:20 AM

#298

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by work_lol

I just tried using Format > "Number" and I was able to enter 17 digits no problem, which version of excel are you using? Is the cell wide enough to show 17 digits?

Quote

01-18-2011 , 10:54 AM

#299

work_lol

grinder

Join Date: Oct 2007 Posts: 493

I believe its 2003. I expand the cell so it should be wide enough and after I enter the numbers and go to the next cell, the numbers convert to scientific notation. For ex:

12345678998787 goes to like 1234E+10 or something like that

Quote

01-18-2011 , 12:32 PM

#300

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by work_lol

Right click on the cell > Format > Number (set decimal places to 0), works fine for me

alternatively you can put an apostrophe in front i.e. '12345678998787 but i wouldn't recommend it

Quote

Page 12 of 120

First

2 7 8 9 10 11 12 13 14 15 16 17 22 32 62 112

Last

Post Reply Subscribe

...

Page 12 of 120

First

2 7 8 9 10 11 12 13 14 15 16 17 22 32 62 112

Last