Excel Magic Trick
Вставка
- Опубліковано 25 сер 2024
- Download Excel Start File: people.highlin...
Download Excel Finished File: people.highlin...
Full Page With All File Links: people.highlin...
See how to create a Dynamic Range with the OFFSET function so a Macro to Create a Pivot Table will work even when new records are added. This method works in all versions.
Dynamic Range with the OFFSET function formula.
Whenever I have time my hobby is watching your tutorials even if it's for 11 years ago, since may find new tip or trick on it as always.
Even if I know the topic, it's a good review.
May God bless you.
Hope see you some day.
Thanks for all of these great trainings.
Just wanted to say thanks for this video. I spent all day researching how to do this yesterday, then stumbled on your video this AM. I applied it to a macro I'm building for a huge report and it worked perfectly! Thank you again!
FYI, I'm using Excel 2013
Thank You Bud! for sharing this, it helps me today to complete a task assigned to me...very nice video done with proper narrations..Hats Off...
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.
Thanks for sharing these tricks. Your videos are really useful in practice.
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!
Fantastic job, thanks....
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
Great video!! Keep up the good work
You are awesome mate!!! great tip!
Thank u for this great video But any one working with Excel 2010 will face a Run time error ... for every one facing run time error , if u are creating the pivot table in the current sheet I suggest to create empty pivot first and after that when u start to creat the macro u need to 'Clear All' from the (pivot table tools menu ) first then continuo create the macro . so the compiler could find the table name in the sheet other wise it will be doesn't exist for the VB compiler ! I spend a week to solve this but this video solve months of analysis for me THANKS !
Thank you very much!
Aewsome video!
WELL DONE! IT IS GREAT! I CAN DO PRINT PIVOT TABLES WITH PRINT AREA :) NOW I CAN TRY DO MACRO WITH THIS :)
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
Thank you.It helped a lot
Great dear. Its is help me lots on my work .
outstanding job!!
very good
Great lesson...thank uuuu
Thank you, It is working with larger data :)
It depends. Are there duplicates in the column? Are the latest dates at the top, bottom, always the last record? If there are no duplicates, then something like:
VLOOKUP(MAX(), where MAX is the lookup value.
Last in column, then use Big Num concept:
VLOOKUP(9.9E+307, where 9.9E+307 is lookup value.
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.
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!! :-)
Superb !!
Nice video
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).
I had 1 issue when recording the macro. I tried to create the pivot table on a new worksheet and it created a reference sheet when recording, which made the macros only work once then result in errors afterwords due to auto assigning worksheet numbers in order of creation. I just deleted a couple lines of macro at the end and changed table destination to "" instead of sheet# and it fixed the issue.
Aaawesome tip. But somehow this offset formula not working on my sheet.
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 ?
Suppose If I would like to open pivot table in other list then what changes I need to do ? I tried it but after one time, it show errow :( I think I have to change some thing in VBA programming ? Please help me to fix this.
I have a question regarding vlookup or look up functions. How to pick latest or newest date value by any look up function
I want to know how to create automated stock analysis chart in Ex-cell,
Hi, I received the same message as jesslynanime - "error run time 5 and invalid procedure call and argument". Could you give me an advice? Thank you!
"invalid procedure call argument"....please help
Hi, I followed your steps , but error run time 5 and invalid procedure call and argument. Please help me. This was highlighted with yellow
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Dynamic", Version:=6).CreatePivotTable TableDestination:="Sheet2!R3C1", _
TableName:="PivotTable1", DefaultVersion:=6
Have you had any response to your Run Time 5 Error problem jesslynanime?
This doesn´t work with Excel 2010. Is it normal? The Pivot table wizard does not accept the name. Can you help me?
I do not know VBA. Try:
mrexcel [dot] com/forum