The Excel Questions Thread

[Biggskip]



Is there a formula to take a date in a cell? I have a column of (lots of) deliveries but they're in the format of "Delivered - 31/08/2012" or "31/08/2012 - delivery to home address" etc. I want to make a column which would have cells with dates in.

If the format of the date, regardless of position in the string, will always be dd/mm/yyyy the following gives you a numerical date (rather than a text value):

=DATE(VALUE(MID(A2,FIND("/",A2)+4,4)),VALUE(MID(A2,FIND("/",A2)+1,2)),VALUE(MID(A2,FIND("/",A2)-2,2)))

 

[iroh]

Is LibreOffice Calc as good as excel? What are your thoughts on Calc?

 

[otc]



Is LibreOffice Calc as good as excel? What are your thoughts on Calc?

No.

But unless you are doing the kind of stuff that people are asking about in this thread, LibreOffice/OpenOffice/etc is probably good enough.

 

[iroh]

I really wish people would use LibreOffice more, seems 90% of the people out there don't need the hardcore features that Office has, they could just save their money and go with the free LibreOffice. I have been using for the past year and it is really decent and I know it would suit lots of people if they are just willing to give it a chance.

(Just remember to change the default save file format to the dominant Microsoft version, for example .odt -> .doc, in the LibreOffice settings, else you won't be able to open your files in Office if you transfer them to another machine).

 

[Sir Humphrey Appleby]

Yeah for most people it works the same, apparently live feeds don't work so great in Libre Office and the programming language will be different, both of which I use extensively, maybe less powerful. I use Excel at work though as do most people, I bought it at home to practice on and test ideas at work, at home the most complicated function I really use is sum.

I'm starting on a reworking of my main spreadsheet which will make it so powerful and complicated I need to plan it out like I'm writing a story, and today I made a spreadsheet that was 34mb and the page with the data has 200,000 rows on it, these two facts both make me happy. Am I a sad bastard?

 

[otc]

Anyone have an archive of the original version of this?:
http://peltiertech.com/WordPress/broken-y-axis-in-excel-chart/

It looks like Jon Peltier decided he didn't like broken axis and removed his tutorial and replaced it with a multi-panel alternative. On one hand I think it is nice that he is sticking to his guns and not telling people how to do something he feels is wrong (even though he had a good answer to their question).
On the other hand I think it is kind of a huge douche move. His guide was good--so good that it is basically the only thing cited online when anyone asks how to make a broken axis in excel (often Peltier himself is the one responding to the question and he says "look at my tutorial here:"). It is made worse by the fact the post is still titled "Broken Axis" and he includes a fricking picture of the exact chart people want right at the top of the page. Google basically only returns results that point to his page (or a really old alternative that looks awful) and he set up a robots.txt file to explicitly prohibit the Internet Archive from keeping an old version of his tutorial--this is the part that I think really makes him a dick since he has eliminated all record of a pretty clever technique...

I understand that the broken axis kind of sucks and is misleading but sometimes you just want to use it. Who wants to try and explain his funky multi-panel chart to a jury when you could break the axis and make an outlier go away (while still having it be obvious that the outlier far exceeds everything else)?
I remembered the method he used to accomplish this trick--but it took a while to figure everything out again since it isn't all obvious from looking at an old excel file...and honestly my preference would have been to give that link to an analyst and let them figure it out.

 

[Sir Humphrey Appleby]

Bump.

I'm trying to write a macro which will fill in tables for me at a certain hour, I can get a web query, refresh it and copy the data into a cell but if I do that it won't wait for the query to finish downloading. Is there a way to delay it until the previous action is finished?

 

[Biggskip]



Bump.

I'm trying to write a macro which will fill in tables for me at a certain hour, I can get a web query, refresh it and copy the data into a cell but if I do that it won't wait for the query to finish downloading. Is there a way to delay it until the previous action is finished?

So is the web query embedded in the spreadsheet? If so, I wouldn't think that the macro would proceed until the query refresh has been completed, but this may not be the case for you.

I can think of two possible solutions. First, you could tell the macro to wait for a specified period of time, which should give the query enough time to finish before carrying out the remainder of the code in the macro. Here is an example I found for how to write in a waiting period:

Use this code for waiting 5 seconds

Sub Wait()
' Waits 5 seconds
Application.Wait Time + TimeSerial(0, 0, 5)
' Continues here after pause
End Sub

Second, say you know that the query will finish at 6:30 a.m. every day. You could set up a "Scheduled Task" in Windows that would open up the file, the file could run the macro upon opening. The Scheduled Task could be set up to run a some specified time that would be well after you know the query will have been competed each day. Here is a link that talks about how to set up a Scheduled Task.

 

[jgold47]

Hi everyone - I have a spreadsheet (2 actually) that have always been a bit bloated. They are finance spreadsheets created by hardcore finance guys. If I'm good at excel, these guys are wizards. While investigating the bloat, I noticed that there are a ton of named ranges in the document. I know nothing about named ranges as I dont use them. based on the path's for some of them, they are years and years old. Here is my question since I cant ever these guys to pick up the phone

1. are there programs or macros that automatically add named ranges to every sheet that you create? Some of the path's and range names are very computer-y. _sa3coe or 4564654-4564654-7789.

2. If you delete a named range, does excel automatically convert the named range back to the string it referenced?

3. By adding a named range to sheet, you do that to facilitate that sheet only right? So if range jgold47 is named, and it references another document. that named range is only available in that original document?



By deleting the ranges I dropped the file sizes by 1mb which is what I was going for. I am just concerned that someone else put those there for reason. I fully own one of the sheets and I know who uses it, but I dont know if it gets passed around to people who work with those things on the back end.



thanks

 

[otc]

If they are really wizards, why are they making documents that are nasty and bloated and require you to go through and fix them.

However, I am not sure how deleting named ranges could save space. Named ranges are not complicated things...so I wouldn't be surprised if you are breaking things by removing the named ranges. Someone put them there for a reason so its probably not a great idea to remove them.

 

[jgold47]

Without sharing too much, there are about 200 named ranges, almost all of them pointing to stuff from years and years ago (2004 budgets, etc...) and almost all of them are #ref. I was looking back at some really old versions of this sheet and all of them showed up in the last year or so, after giving back to the finance guys to do some formula tweaks. My guess is that this stuff got appended somehow. I've found versions of these names in almost all of the sheets coming from these guys, almost all of them are #ref.

It certainly doesnt break anything when I delete them, but I am worried someone else may come looking for them.

 

[rajesh06]

Here are some reponses
1 Some add-ins do create named ranges; you can also write macros to do that
2 No
3 Named ranges can have either a worksheet "scope" or workbook "scope" (Formula Tab / Name Manager). You can refer to cells by range names between documents but you are correct that the name itself only exists in the workbook with the named range.

Hi everyone - I have a spreadsheet (2 actually) that have always been a bit bloated. They are finance spreadsheets created by hardcore finance guys. If I'm good at excel, these guys are wizards. While investigating the bloat, I noticed that there are a ton of named ranges in the document. I know nothing about named ranges as I dont use them. based on the path's for some of them, they are years and years old. Here is my question since I cant ever these guys to pick up the phone

1. are there programs or macros that automatically add named ranges to every sheet that you create? Some of the path's and range names are very computer-y. _sa3coe or 4564654-4564654-7789.

2. If you delete a named range, does excel automatically convert the named range back to the string it referenced?

3. By adding a named range to sheet, you do that to facilitate that sheet only right? So if range jgold47 is named, and it references another document. that named range is only available in that original document?



By deleting the ranges I dropped the file sizes by 1mb which is what I was going for. I am just concerned that someone else put those there for reason. I fully own one of the sheets and I know who uses it, but I dont know if it gets passed around to people who work with those things on the back end.



thanks

 

[otc]

Oh...if they point to #REF then I guess they can be deleted (all useful information about them has already been lost)

 

[DextersMorgan]

But seriously I was thinking more generic, along the lines of "how do I consolidate a vertical list of items that are at separated randomly by blank cells? i.e.

from:

a

b
c


d

e

f

to:

a
b
c
d
e
f

?"

Use the sort command HAHAHAHAHA!

 

[Reggs]

How much do all of you do to make your spreadsheets pretty?

I always color headings, center text, use different font in headings, make sure my cell boarders look good, and formatt as table>convert to range to get every other row colored. I also think numbers look best in Calibri. Delete unused tabs, color all used ones based on their purpose.

I think I'm the only one who does this. When I get spreadsheets from other people they are always so ugly.