Dermot's Blog - All Comments

Das Farmville Buch - Tipps und Tricks für Farmville

Das Farmville Buch - Tipps und Tricks für Farmville — Wed, 22 Sep 2010 05:46:35 GMT

Pingback from Das Farmville Buch - Tipps und Tricks für Farmville

1989 - 2000 @ 320i Sale Speed Manual Transmission Bmw 3 Series, Rent 1979 Bmw 320i Body Kits - 439.cmanager.org

Thu, 27 May 2010 14:24:23 GMT

Pingback from 1989 - 2000 @ 320i Sale Speed Manual Transmission Bmw 3 Series, Rent 1979 Bmw 320i Body Kits - 439.cmanager.org

Applying a complex Excel model to multiple input values « St??phane-Robert Langer

Fri, 28 Aug 2009 07:53:47 GMT

Pingback from Applying a complex Excel model to multiple input values « St??phane-Robert Langer

re: How to build an awesome user interface for results

Giff — Mon, 17 Nov 2008 22:29:39 GMT

Beautiful. I worked on a similar project a year ago that ended up with pages of one off VBA to do just this, oh to put this in place instead.

re: How to build an awesome user interface for results

brianbew00 — Fri, 07 Nov 2008 18:13:45 GMT

Hey Dermot,

Have followed the instructions exactly, but the picture box is displaying the range name (eg "table1"). The =picwindow cell with the indirect function in it is displaying the name as well. It is not converting the range name to the actual range. I'm using 2007, any suggestions?

re: Safer business spreadsheets - a practical approach that works

XL-Dennis — Mon, 11 Feb 2008 17:37:57 GMT

Dermot,

This is indeed an interesting area which should get more attention then what it actually gets.

In my experience and from my point of view:

# Spreadsheet policy and standards

Most corporate lack any guidelines in these field. Mainly because Excel is not part of any

"officially" information system, i e it's still viewed as a personal tool.

# Spreadsheet auditions

Many corporate rely on spreadsheet solutions in critical business processes. Despite this

situation there is no spreadsheet auditions done on a regular basis in the internal or

external reviewing.

# Spreadsheet quality

Quality should always be evaluated from two perspectives:

Technical quality of the spreadsheet solutions and

Data quality

# Spreadsheet security

For most people "security" means "lock out" and they complains about Excel's poor level of

security etc.

In my opinion the focus should be on attitudes/behavior as they, in the long

run, guarantee and support 'safe'.

Kind regards,

Dennis

re: Safer business spreadsheets - a practical approach that works

XL-Dennis — Mon, 11 Feb 2008 17:30:55 GMT

Dermot,

This is indeed an interesting area which should get more attention then what it actually gets.

In my experience and from my point of view:

# Spreadsheet policy and standards

Most corporate lack any guidelines in these field. Mainly because Excel is not part of any

"officially" information system, i e it's still viewed as a personal tool.

# Spreadsheet auditions

Many corporate rely on spreadsheet solutions in critical business processes. Despite this

situation there is no spreadsheet auditions done on a regular basis in the internal or

external reviewing.

# Spreadsheet quality

Quality should always be evaluated from two perspectives:

Technical quality of the spreadsheet solutions and

Data quality

# Spreadsheet security

For most people "security" means "lock out" and they complains about Excel's poor level of

security etc.

In my opinion the focus should be on attitudes/behavior as they, in the long

run, guarantee and support 'safe'.

Kind regards,

Dennis

re: An awesome powertool buried in Excel

Randy — Wed, 23 Jan 2008 17:27:48 GMT

Thanks so much for that tutorial on Data Tables. I'm a geotechnical engineer (civil engineer working with soil), and we have many, many custom spreadsheets for our calculations. I've used VBA quite a bit for some of our more complex modeling. Now I look at your examples and I think in many cases I can use this approach and keep it much simpler. Many thanks!

re: An awesome powertool buried in Excel

Barefoot — Wed, 23 Jan 2008 13:56:55 GMT

Dermot

Thanks, very interesting. This is the thing I really like about Excel - there are so many different things to learn and master! and there is always more than 1 way to do something!

Again thanks, excellent post.

Julian

re: A little library of gems for VBA

Nick Hodge — Fri, 18 Jan 2008 00:01:23 GMT

Dermot

That's interesting as to the breadth of what you can do. I've only ever really needed those exposed by the environs function though.

Sub test()

Dim x As Integer

For x = 1 To 50

Debug.Print "No." & x & " " & Environ(x)

Next x

End Sub

re: How to build an awesome user interface for results

Harald Staff — Wed, 16 Jan 2008 13:35:41 GMT

Nice !

I use pictures on DialogSheets from time to time (No can do on Userforms for some odd reason). A live snapshot of a hidden worksheet has tons of possibilities.

re: How to build an awesome user interface for results

Roger Govier — Wed, 16 Jan 2008 09:26:56 GMT

Hi Dermot

Very nice implementation.

I have used the camera tool before to show small pie cahrts alongide tables to give a nice visual effect when the user changes slection of data within the table.

I had never thought of using the camera in this way.

It presents a very nice clean interface for the user.

Thanks for sharing.

Roger

re: A blog for Excel users rather than developers

Handy Excel Tips — Mon, 14 Jan 2008 18:36:16 GMT

Oh my God!! I don't believe it! I am also an actuarial student (not an actuary yet) with an life insurance company and have recently started a blog site to help people with excel.. http://www.handyexceltips.com

re: Creating Word reports from Excel

Nick Hodge — Mon, 14 Jan 2008 13:17:02 GMT

Not looked at the workbook Dermot, but nice useful everday post. If the file gets a good uptake, maybe also post it in the file area?

re: Using hyperlinks for navigation in Excel

Omar — Sat, 12 Jan 2008 13:33:48 GMT

Another problem is the hyperlink breaks if you are linking to another file and you change the path to the other file.

I wanted to use hyperlinks to open another file (say a pdf with further product information). The two files are always together in the same folder, but the path could change depending on what computer everything ended up on.

I ended up creating a formula that put the path of the master spreadsheet into a cell. I'm sure I relied on some blog post somewhere for this!

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

I created a second formula that added the desired filename and this path together. If the path is in A1 and the file name is in A2, this formula would be =A1&A2

Finally, I had the hyperlink refer to the second formula. All this could be repeated several times. I have a method to put the hyperlink on a report tab, and then hide the working tab.

All this was so my boss could add a link to another file he had created without having to learn hyperlinking. We wanted to take the end result and publish the spreadsheet and all supporting files to multiple locations without having to worry about the path to those files.