# Somewhere out of left field........



## DiscoPistol (Apr 7, 2009)

As someone who considers themselves more than competent with Excel/VBA, over the years the occasions when something has come out of left field and left me agog (great word) have become few and far between.

But today, whilst looking at some OLD company spreadsheets I came across DialogSheets for the first time. Tré Cool!

So when was the last thing that you guys found something that left you both open mouthed and with a inane geeky grin on your face?


----------



## xld (Apr 7, 2009)

DialogSheets ... cool? In what way when you have VBA?


----------



## DiscoPistol (Apr 7, 2009)

In a Nostalgic way. I'm not advocating there use in everyday life (and indeed am currently burning them all at the stake where I've found them)


----------



## xld (Apr 7, 2009)

DiscoPistol said:


> In a Nostalgic way. I'm not advocating there use in everyday life (and indeed am currently burning them all at the stake where I've found them)



LOL! Quite right too.

Having said what I said, and making no apologies, but I do sometimes use them to create a dynamic form, such as one that has a number of option buttons based upon a table, with a simple action basis.


----------



## Richard Schollar (Apr 7, 2009)

I would place Roger Govier's method for turning a denormalised table into a normalised one using Pivot Table Multiple Consolidation Ranges - I would have sworn before the Excel User Conference that MCRs had exactly no actual use at all.  Finding that in fact they do (and it's a pretty good one at that!) was something of a revelation!


----------



## Patience (Apr 7, 2009)

Ah Richard. I had a mini mental orgasm at that part. Sublime and beautiful. Coulda saved me 6 months last year if I knew it before - but there's always next year.


----------



## xld (Apr 7, 2009)

RichardSchollar said:


> I would place Roger Govier's method for turning a denormalised table into a normalised one using Pivot Table Multiple Consolidation Ranges - I would have sworn before the Excel User Conference that MCRs had exactly no actual use at all.  Finding that in fact they do (and it's a pretty good one at that!) was something of a revelation!



Yeah, but he stole my thunder. I had that as part of my visualisation presentation (and I had tons of spare time).


----------



## SydneyGeek (Apr 9, 2009)

Sounds great -- so, how's it done? 

Denis


----------



## xld (Apr 9, 2009)

Is that directed at me?


----------



## VoG (Apr 9, 2009)

DiscoPistol said:


> In a Nostalgic way. I'm not advocating there use in everyday life (and indeed am currently burning them all at the stake where I've found them)



This may prove handy http://j-walk.com/ss/excel/files/dlgwiz.htm


----------



## DiscoPistol (Apr 9, 2009)

VoG said:


> This may prove handy http://j-walk.com/ss/excel/files/dlgwiz.htm



Thanks!

Although it will probably take less time to rewrite the Spreadsheet than it will take IT to give me permission to download the file


----------



## Oorang (Apr 9, 2009)

DiscoPistol said:


> So when was the last thing that you guys found something that left you both open mouthed and with a inane geeky grin on your face?


The last thing of awesomeness I found was LSet/RSet could copy values in UDTs back and forth no matter what the types were, as long as they have the same number of bytes. A lot of things I used to use RTLMoveMemory can be accomplished this way. Below is a trivial example:


```
Option Explicit
Private Type TypedLong
    Value As Long
End Type
Private Type Color
    'Has to be in this order
    Red As Byte
    Blue As Byte
    Green As Byte
    PadByte As Byte
End Type
Sub Example()
    Dim clr As Color
    Dim lngClr As TypedLong
    lngClr.Value = 10905400
    LSet clr = lngClr
    MsgBox Join(Array(lngClr.Value, "Red:=" & clr.Red, "Blue:=" & clr.Blue, _
        "Green:=" & clr.Green), vbNewLine)
End Sub
```


----------



## SydneyGeek (Apr 16, 2009)

xld said:


> Is that directed at me?


 
Hi Bob, Yes -- I would love to know. Sounds like something I could definitely get some use out of.

Denis


----------



## xld (Apr 17, 2009)

Denis,

Here are my notes that I use, based on Excel 2003. Hopefully it is self-explanatory

Menu>Data>PivotTable and PivotChart Report
Select the Multiple consolidation ranges option
Next
Select the option to create the page fields yourself
Next
Select the source table (or that part of) to be changed, and click the Add button
Next
Pick a location for the pivot table, AND THEN click the Layout button
In this dialog drag both of the Column and Row buttons out of the pivot schematic, leaving just the 'Sum of Value' data field
OK
Finish

This should create a single item pivot
Double-click the cell that contains this single item total, and Excel will create a new sheet with the original data flatfile form
Change the column headings to something more meaningful


----------



## Richard Schollar (Apr 17, 2009)

xld said:


> Double-click the cell that contains this single item total, and Excel will create a new sheet with the original data flatfile form


 
Gaarrrgghhh!!  I knew I'd forgotten something!! I was copying & pasting values over the PT and then manipulating the formatting of the data 

Thanks Bob!


----------



## SydneyGeek (Apr 17, 2009)

Bob, that's a great trick! 

Going to add it to my toolkit...

Denis


----------



## Fazza (May 4, 2009)

John Walkenbach's site has had that tip for some years, I believe.

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/


----------



## lenze (May 4, 2009)

DiscoPistol said:


> Although it will probably take less time to rewrite the Spreadsheet than it will take IT to give me permission to download the file


You never *ask IT* to let you download a file. You have your boss's boss tell *them* to download it for you. Always worked for me 
lenze


----------

