Excel Query

[BlindFish]

Hope someone out there has an answer to my two little queries

1. Is it possible to take the contents of an 'autofilter' list and put the values into a column on a separate sheet? ie - in a column with repeated values of a, b and c when you click on the little down arrow in the column header the autofilter list = a, b, c. I want to be able to take these values and put them on a separate sheet. I'm currently having to do this manually and with 60 different entries it's becoming a little tiresome to keep track of any changes.

2. I'm not that experienced with macros and am having a slight problem: I've got a spreadsheet that is saved under a different file name each month. I've set up buttons linked to macros stored within the spreadsheet - the problem is that when the spreadsheet name changes and you click the button, Excel wants to open the file the macros were originally stored under. I'm assuming it's possible to store the macros somewhere other than within the spreadsheet but I haven't worked out how to do this yet.

Any help would be greatly appreciated

[David_Jones]

1) I solved this for myself a while back using formulae.

Basically, I put a new column (say, 'B') in the sheet with the list (assume in column A, starting row 1), that contained a fomula "=countif(A$1:a1,a1)"

Copy that down as far as the list goes, and note the single $ in there.

It will calc a number telling you how many instances of the item in column A were found up to and including that row. Each new unique entry, will have a '1' beside it (next occuance will have a '2' and so on'.

Next add another new column (say, 'C') that has the formula:

"=countif(B$1:b1,1)" and copy that down too.

That will now give you a reference (1..n) for each unique entry in column A.

Finally, you can use a lookup formula to get the unique entries all together, by utilising the references in column C.

May not be the most elegant solution but it worked for me!

HTH,

David,

[David_Jones]

2) Edit the macro to remove the file name references.

To get into the VBA editor, press Alt-F11

Select your macro from the modules listed on the left hand side, and you will see the VBA code in the main (RHS) pane.

You can edit that text.

HTH,

David.

[BlindFish]

Thanks a lot for these suggestions... I'll give them a go.

Cheers

BlindFish

[BlindFish]

Well I tried David's method for my first query but it wasn't quite what I was after and still a touch complicated. But I have found a solution, here:

http://www.mrexcel.com/tip031.shtml

Basically there are these wonderful things called CSE formulae that can be used to do the same as COUNTIF (or lots of other things) and use more than 1 criteria.

For COUNTIF use the formula:

=SUM((1st range of cells="1st criteria")*(2nd range of cells="2nd criteria")*1)

The important thing to do is when you've typed in the formula don't just press Enter. You have to hold down Control and Shift and then press Enter for it to work.

I haven't quite understood what's going on here yet as I only just found out about it but I've also just found this site http://www.cpearson.com/excel/array.htm that seems to explain it...

I'm still not sure about the solution to my second query... I've got no problem getting into the VB editor but I can't find any reference to a file name within the Macro... I'll persevere ...

[David_Jones]

Post the macro here

[BlindFish]

hmmm... there's little point in doing that. it's a pretty simple macro - so it's not a case that I can't find it in the pages of code - it simply isn't there.

I notice that I can export a macro as a .bas file. Can I get custom buttons to use this file as the source for the macros?

[David_Jones]

What object is the macro operating on?

[BlindFish]

According to the 'explorer panel' on the left it's under 'VBAProject(Filename)\Modules' and not associated with a particular Object. In fact opening each of the 'Microsoft Excel Objects' (Sheet1, Sheet2 and 'This Workbook') each of these is blank.

As I said before - I'm no expert on this macro malarkey - so maybe I'm overlooking something really stupid. Whatevr - it's clear from the Visual Basic editor that the macro is associated with each individual file - and I want it stored in a central location or within the copy of Excel (whichever is easiest - though central location would make more sense in case it needs amending at a future date) so it doesn't try opening the original file the buttoin is associated with when the file is saved under a different name.

Thanks a lot for you perseverence.

BlindFish

[David_Jones]

Put it in your personal.xls file, then it will always be available when Excel is opened.