Interesting idea. Would not using a split so you could scroll down in the bottom pane, whilst keeping the table/sumif output in the top pane in view be easier.
I have wanted to do this for the better part of a decade now! I asked everyone I knew and NOONE knew how to do this! THANKS! You simply have no idea how much this helped!
Make the totals a separate sheet. Open a new window with that sheet. Stick it to the right side of the screen - your sheet is empty there anyways. Stick your data window to the left side of the screen.
My sheet is empty, someone else's might not be. This is just a sample data, if you have something that spans dozens of columns, you are stuck with scrolling if you use a multi window setup.
@@XcelsiveEnglish in that case, I usually transpose the totals to fit them in 1 or 2 rows, insert them above the column titles, and freeze panes if they're not already so that the title rows and totals are always visible
Excellent!! The floating table image is a genius solution for several problems I’ve faced! For this specific example problem, I probably would have just added another column to the existing table with the sumif formulas in it… but that’s probably because I had no idea about the floating table images!
@@XcelsiveEnglish I make a lot of Excel tools for other people at work. The latest is a worksheet for selecting only certain things from a much larger sheet that meet given criteria, and thus populating a form. What it doesn’t do is give a clean visual of everything you’ve chosen so far at a glance for reference, and this would work perfectly for that while not interfering with any of the surrounding formulas
Why not freeze the top few rows and use your table idea? I love the image idea though. It gives me other options to think about. Thank you for the video.
Great video! I tried copying and cutting the picture to other locations and everywhere it failed to work. That is, I copied it to another sheet within the same workbook, another sheet in a different workbook, into the body of an email, into a chat message in Teams and in all of those places the picture stopped functioning as a viewer/image to the data. If MSFT could implement this in those locations whilst keeping it dynamic then it would be a real game changer!! Data engineering would be changed over night!!
@@edsta714 I can't get it to work on a different worksheet. I can't copy or cut it anywhere pasting as anything (picture or otherwise) without it failing. It works only within the same worksheet. So, it is good functionality but very limited in scope.
I tried that too, if it had worked, it would have been a fitting climax to my video, but unfortunately I had the same results like yours. Your idea of using it emails, chat messages etc if implemented, could be revolutionary!
I have tested it, it works fine. I populated the item list with UNIQUE and then used SUMIF in the next column. I guess I should have used UNIQUE in my video and made the process future proof 😎
You're right, it could replace the pivot table, but the issue of scrollable and always accessible results would still have remained. By the way, I have a video about PIVOTBY & GROUBY here th-cam.com/video/KPUovarnAXE/w-d-xo.html
Image camera feature is new to me so this is fantastic to see. This doesn't solve the problem of the image window being static tho... A true solution is - Pivot on another sheet, add another workbook view, split the screen with the two windows, simplez! Then you can scroll down one sheet and the other stays static. The auto refresh of pivot tables is probably solvable with VBA. OR don't use a pivot but build your own set of formulae.
Splitting the screen is the most common suggestion I have received for this video. I normally avoid that coz it means limiting my working area. About using VBA to update pivot table, I wanted something that is easy enough for beginners. Furthermore, even if was able to update the pivot table automatically the issue of it being stuck at one position still remains.
@@XcelsiveEnglish I have never used Camera tool because I never had the need. I'd resort to VBA for this kind of stuff because there is no other way around it. For ease of use for n00bs, VBA could be shared via a module or an add-ins perhaps. We all have to start somewhere and if we don't, we will always be n00bs. Having said that, I'm not undermining your work in anyway but more like trying to prove that it doesn't have to be this and this only. I said what I said because if VBA were already considered for refreshing Pivot table, moving it shouldn't be a limiting factor for not using VBA. If I were to figure out a solution, I'd use a modeless userform with something like a listview containing a portion of the required range and as bonus feature, I'd probably make the userform transparent and even click-through-able since you always make a point to emphasize screen real estate. Having said that, the code wouldn't be that hard (but not n00b-friendly for sure) but reusability would be very high, thus, so much fun. Now that, I thought of it, I'd probably make it after my current personal VBA projects are finished!
Interesting idea but doesnt solve your pivot problem: from what i could see you copied the pivot table items then applied a SumIf formula. If you changed an Item description, added an item or removed it, the 'pivot' would collapse. Try using the Unique Formula then SumIf. That way if the Item description changed, the 'pivot' would change too. The normal pivot table would of course work, just in the same location and refresh.
That's a pretty good suggestion. Thanks. It didn't occurred to me that there may be any change in my table. I did tried your suggestion and it works flawlessly.
Interesting idea. Would not using a split so you could scroll down in the bottom pane, whilst keeping the table/sumif output in the top pane in view be easier.
That's a great idea! However, you will be sacrificing the screen real estate by doing so.
OR make all your changes and then refresh the pivot table only once 😛
That could work, only until we make a mistake and realise it only after finishing the file and finally refreshing the pivot table 😄
Amazing creativeness and appreciate it
Thanks for the support
I have wanted to do this for the better part of a decade now! I asked everyone I knew and NOONE knew how to do this! THANKS! You simply have no idea how much this helped!
Glad I could help!
great idea, thank you for sharing
Thanks for watching!
This is smart!
Thanks
You can split and create a new window of the same worksheet. Both solves the issues of clicking and dragging the camera image
That would sacrifice the screen real estate.
Make the totals a separate sheet. Open a new window with that sheet.
Stick it to the right side of the screen - your sheet is empty there anyways.
Stick your data window to the left side of the screen.
My sheet is empty, someone else's might not be. This is just a sample data, if you have something that spans dozens of columns, you are stuck with scrolling if you use a multi window setup.
@@XcelsiveEnglish in that case, I usually transpose the totals to fit them in 1 or 2 rows, insert them above the column titles, and freeze panes if they're not already so that the title rows and totals are always visible
that would work for you too.
Excellent!! The floating table image is a genius solution for several problems I’ve faced! For this specific example problem, I probably would have just added another column to the existing table with the sumif formulas in it… but that’s probably because I had no idea about the floating table images!
Glad that I was able to help. Out of curiosity, what other problems you've faced that could have been solved by this?
@@XcelsiveEnglish I make a lot of Excel tools for other people at work. The latest is a worksheet for selecting only certain things from a much larger sheet that meet given criteria, and thus populating a form. What it doesn’t do is give a clean visual of everything you’ve chosen so far at a glance for reference, and this would work perfectly for that while not interfering with any of the surrounding formulas
Use the unique function and/or some of the advanced filter function or hash operator magic to get you pivot replacement more flexible.
Thanks for the suggestion
Why not freeze the top few rows and use your table idea? I love the image idea though. It gives me other options to think about. Thank you for the video.
That works too, if you don't mind sacrificing your screen real estate!
Interesting solution. If you have 2 screens available, then View > New Window and drag the new window to the 2nd screen
That is, if you have a multi monitor setup. 💰💰
It seems you have done this work from the heart! Can you give me some tips after looking at my project too?🙏
I have, thanks for noticing. Sure I can, share the file link here and I'll do what I can
No need to dig for the camera-icon. Just copy your range and paste it as a Linked Picture.
possibly. I haven't tried it myself
Great video! I tried copying and cutting the picture to other locations and everywhere it failed to work. That is, I copied it to another sheet within the same workbook, another sheet in a different workbook, into the body of an email, into a chat message in Teams and in all of those places the picture stopped functioning as a viewer/image to the data. If MSFT could implement this in those locations whilst keeping it dynamic then it would be a real game changer!! Data engineering would be changed over night!!
I haven’t tried the camera but copy paste special linked pictures does the same thing. You can cut it into other worksheets.
@@edsta714 I can't get it to work on a different worksheet. I can't copy or cut it anywhere pasting as anything (picture or otherwise) without it failing. It works only within the same worksheet. So, it is good functionality but very limited in scope.
I tried that too, if it had worked, it would have been a fitting climax to my video, but unfortunately I had the same results like yours. Your idea of using it emails, chat messages etc if implemented, could be revolutionary!
Could you explain your method a bit?
It sounds like this is probably coming with future Microsoft Loop components.
cool
Thanks
Might be nice to use Unique for the item name. if you add a new item, it would pick it up too. Not sure how the total would work out though.
I have tested it, it works fine. I populated the item list with UNIQUE and then used SUMIF in the next column. I guess I should have used UNIQUE in my video and made the process future proof 😎
As it rolls out, PIVOTBY is a great option here too
Just saw a video on PIVOTBY just the other day. Waiting for a reason to use it. :)
You're right, it could replace the pivot table, but the issue of scrollable and always accessible results would still have remained. By the way, I have a video about PIVOTBY & GROUBY here th-cam.com/video/KPUovarnAXE/w-d-xo.html
May be this could sway you, th-cam.com/video/KPUovarnAXE/w-d-xo.html
You can use this to put a live excel table inside other MS programs also.
You can, but it wont update when values are changed in Excel
Excel has a watch window feature, you could set it to watch your table
It does. And it looks terrible!
I need this but I also need to be able to filter
Image camera feature is new to me so this is fantastic to see.
This doesn't solve the problem of the image window being static tho...
A true solution is -
Pivot on another sheet, add another workbook view, split the screen with the two windows, simplez!
Then you can scroll down one sheet and the other stays static.
The auto refresh of pivot tables is probably solvable with VBA.
OR don't use a pivot but build your own set of formulae.
Splitting the screen is the most common suggestion I have received for this video. I normally avoid that coz it means limiting my working area. About using VBA to update pivot table, I wanted something that is easy enough for beginners. Furthermore, even if was able to update the pivot table automatically the issue of it being stuck at one position still remains.
@@XcelsiveEnglishVBA can move the pivot table as well...based on Selection Change or Change Event...
Agreed. But I needed something simple enough that beginners can use too. VBA is a tad bit advanced don't you think?
@@XcelsiveEnglish I have never used Camera tool because I never had the need. I'd resort to VBA for this kind of stuff because there is no other way around it.
For ease of use for n00bs, VBA could be shared via a module or an add-ins perhaps.
We all have to start somewhere and if we don't, we will always be n00bs.
Having said that, I'm not undermining your work in anyway but more like trying to prove that it doesn't have to be this and this only.
I said what I said because if VBA were already considered for refreshing Pivot table, moving it shouldn't be a limiting factor for not using VBA.
If I were to figure out a solution, I'd use a modeless userform with something like a listview containing a portion of the required range and as bonus feature, I'd probably make the userform transparent and even click-through-able since you always make a point to emphasize screen real estate.
Having said that, the code wouldn't be that hard (but not n00b-friendly for sure) but reusability would be very high, thus, so much fun. Now that, I thought of it, I'd probably make it after my current personal VBA projects are finished!
I agree. But from my position, I am still trying to find my niche here. If in future, more tech savvy videos perform better, you will see those here.
Interesting idea but doesnt solve your pivot problem: from what i could see you copied the pivot table items then applied a SumIf formula. If you changed an Item description, added an item or removed it, the 'pivot' would collapse. Try using the Unique Formula then SumIf. That way if the Item description changed, the 'pivot' would change too.
The normal pivot table would of course work, just in the same location and refresh.
That's a pretty good suggestion. Thanks. It didn't occurred to me that there may be any change in my table. I did tried your suggestion and it works flawlessly.
wow
Data set link sir
Here it is tinyurl.com/tybjevpd
Also what is with the channel name??
What about it?
Shame it isn't a pane.
How do you mean?