MS Excel / VBA help thread

Got a question about the world's greatest software programme, Microsoft Excel, that needs answering? Frustrated by functions? Antagonised by arrays? Vexed by VBA? Post your question here (or better still, find your question already answered by someone!), and perhaps someone can help...

had you posted this thread 6 minutes ealier it would have been brilliant, but i just finished my homework

Why is it, when I use a pivot table in a spreadsheet with Macros, my data moves around so that it's full on WRONG?
(Records get mixed up all over the place)
(My solution is not to use the two together)

Can you be a little more specific?

Some users use a certain type of thing. When my FischerPrice Macro colour codes and is done, I thought it might be interesting to pivot table my data and present it better. Unfortunately, I did this. A user who has NEVER used a certain type of thing has suddenly been ascribed to using this thing. Fortunately, I spotted this as an error and didn't make any calculations based on this.

If you've made changes to the data on which the pivot table is based, you will need to refresh it. Does this help?

This thread will come in very handy.

If you've made changes to the data on which the pivot table is based, you will need to refresh it. Does this help?

It's not the pivot data that messes up. It's the original table. It's very weird. It's an anectodal case report looking for someone to see if they've also experienced this.

Warning to anyone who might have Microsoft's very own PowerPivot AddIn installed ... it screwed up the 'Save' and 'Save As' functions in Excel (click and it does nothing - which you don't realise until way too late). After losing a day and a half's worth of work I eventually sussed that the only way I could save was to send as an attachment and save the attachment.

Removing the PowerPivot AddIn solved the problem.

Microsoft cannibalismo.

Do you have the pivot table on the same sheet as the source data or on a separate tab?

Some of your conditional formatting might now be referencing pivot table cells if you have used absolute cell references and then inserted rows/columns to insert the pivot.

Always easier to check if you separate the original source data and the views of the data (here's a tab with conditional formatting applied, here's the tab with the pivot table) into separate tabs.

Pivot on separate tab in same work book.

Never seen what you are describing happen. Pivot tables only read the source table they cannot change it at all.

I know. That's what I thought. It's all a bit odd. Anyway. I don't do it now.

Is there something in your code that is triggered by an event on the pivot table?

Not that I can think of. I've just had a play with it again, not repeating this time, but it's happened before. Does it make a difference if the original sheet has autofilter on?

Shouldn't do, unless you're actually changing the range that the PT caches from when you change the filtering. Do any of your VBA lines seem to be changing the range that the PT pulls data from?

I don't know crap about macros, and would rather avoid for this application...But, I still want merged cells to expand to fit bigger bunches of text (especially when it's summed from another cell on another sheet).
It's just impractical* not *to have some merged cells on my work, can't see a way around it.
None of the formulas I've found on the innernet work, neither does auto row height. Although I need it to be the same for all cells whether merged or not.

Manually dragging rows seems really cumbersome considering what excel is actually capable of.

This seems like it might be a workaround, but it's a bit cumbersome:
http://excel.tips.net/Pages/T003207_Automatic_Row_Height_For_Merged_Cells_with_Text_Wrap.html

Why is it impractical not to have merged cells? If it's for formatting reasons, you could try turning the gridlines off, then creating some custom borders.

Well, without the merged cells I end up with large chunks of empty space once the text is entered...

I have been trying to design the thing without using merged cells but it's proving difficult. Looks great until large amounts of text are inputted.
I'm already using custom borders.

Merged cells look better, but once the user starts inputting text, they'd have to go over the whole 10 sheets of the workbook manually pulling down row heights.

My current compromise is to not use any merged cells anywhere, which does leave a few blank spaces but doesn't look too bad when printed, certainly no worse than the original word doc which I have redesigned.

VBA for dummies/something similar
Has anyone got a book I can borrow? I'm not having much fun with one I've got from Mr Excel for some reason.

Needs to be 2003 compatible, as that's my work version. Home version is 2010.
fffffffffffffffffuuuuuuuuuuuuuuuuuuu

Try your local library

Good call. Just tried Lambeth Library. They have 2007.....

Managed to find an old copy on Amazon....good old Amazon.
For about a fiver.

I like this thread, I spend most of my working life in excel... Try to avoid merged cells if at all possible, it does make vba'ing around them later on (if required) a bit of a pain.

hmm, anyone help with this nested formula?

=IF(C2="1",G12,IF(C2="2",G14,IF(C2="3",G17,IF(C2="4",G19,IF(C2="5",G22,IF(C2="6",G24,IF(C2="sm",G26,"")))))))

not woking for some reason.

tried this too:

=IF(C2="1",G12,"")&IF(C2="2",G14,"")&IF(C2="3",G17,"")&IF(C2="4",G19,"")&IF(C2="5",G22,"")&IF(C2="6",G24,"")&IF(C2="SM",G26,"")

But it only works with the final value and none of the others..

C2's data is from a dropdown box if this makes any difference

Not getting any error, just a blank cell.
thanks, just going to see how your suggestion goes...