Excel Magic Trick #219: Pivot Table Macro w Dynamic Range #2

[MrSeniorba]

you are awesome mate. One question is:

why not simply choose the data range as "Data2!$A:$D" instead of writing an offest function? what is the difference?  thx mate

[ExcelIsFun]

If you highlight A:D, you will get blanks in the PivotTable. I usually do not like to have blanks in the PivotTable. However, it is really easy to filter the blanks out in the PivotTable. So either way is usually okay!

[nandu2002002]

Fantastic job, thanks....

[glenpoem]

kjhkhkj

[moazzamca1]

Thanks, I will look in to these. Mike I have 3 questions if it is appropriate to ask here.

1. can we have dates grouped in the pivot tables while these are in columns (not rows)?

2.when the dates rows are grouped in a pivot table in weeks, can we change the date format in to dd-mmm-yy?

3.diff. b/w calculated fields and calculated item in pivot table (never tried these).

[ExcelIsFun]

1: Yes

2: Yes, 1) right-click filed, 2) click Field Settings, 3) click Number formatting, click Custom, type custom Number formatting.

3: These are possible, but it is not like formulas in cells, it is like making query formulas in Access. The calculations are always done on aggregate numbers. I think I have only one example of this in the Highline Excel Class Pivot Table videos.

[moazzamca1]

Aaawesome tip. But somehow this offset formula not working on my sheet.

[ExcelIsFun]

Without more detail i am not sure why it did not work. Did you try looking in the Name Manager (Defined Name) dialog box? Sometimes double quotes get put in the formula - if that is the case, delete them.

You can also try these other OFFSET videos:

Excel Magic Trick 584: Dynamic Range for Periodic Data Dumps into Excel OFFSET & Defined Names

Excel Dynamic Chart #10: OFFSET Function

Excel Dynamic Chart #12: INDEX function

[srigce]

Hi Mike,

My doubt is ...

I need the counts of each data. So i tried the Pivot table macro.

But macro is giving the total counts of all data in the table. Is it possible to get the counts of each data in the pivot table using macro ?

[ExcelIsFun]

I am not sure what you mean by data? Is the data words or numbers or TRUE/FALSE?

A worksheet function like COUNT will count numbers and COUNTA will count anything - that is probably the way to go.

[chuco4]

Hello Mike, .....Quick Question...

Is there a way to run this macro in other Workbooks, with out having to work in the same sheet/workbook always?

Its is just what I needed though!!!

Thanks for all the videos, you make it look very easy!! :-)

[ExcelIsFun]

I am not very good at writing VBA code from scratch. Your best bet is to post a clearly asked question (state how data is setup, what you want to do, and what the end result is) to the Mr Excel Message Board. Search for and watch this video title:

How To Post Questions At The Mr Excel Message Board