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!

@XcelsiveEnglish

Ай бұрын

Glad I could help!

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.

@XcelsiveEnglish

Ай бұрын

That's a great idea! However, you will be sacrificing the screen real estate by doing so.

@eleghari

Ай бұрын

OR make all your changes and then refresh the pivot table only once 😛

@XcelsiveEnglish

Ай бұрын

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

@XcelsiveEnglish

Ай бұрын

Thanks for the support

Use the unique function and/or some of the advanced filter function or hash operator magic to get you pivot replacement more flexible.

@XcelsiveEnglish

21 күн бұрын

Thanks for the suggestion

great idea, thank you for sharing

@XcelsiveEnglish

Ай бұрын

Thanks for watching!

This is smart!

@XcelsiveEnglish

Ай бұрын

Thanks

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.

@XcelsiveEnglish

Ай бұрын

That works too, if you don't mind sacrificing your screen real estate!

I need this but I also need to be able to filter

Interesting solution. If you have 2 screens available, then View > New Window and drag the new window to the 2nd screen

@XcelsiveEnglish

24 күн бұрын

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?🙏

@XcelsiveEnglish

Ай бұрын

I have, thanks for noticing. Sure I can, share the file link here and I'll do what I can

You can split and create a new window of the same worksheet. Both solves the issues of clicking and dragging the camera image

@XcelsiveEnglish

Ай бұрын

That would sacrifice the screen real estate.

@BoraHorzaGobuchul

Ай бұрын

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.

@XcelsiveEnglish

Ай бұрын

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.

@BoraHorzaGobuchul

Ай бұрын

@@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

@XcelsiveEnglish

Ай бұрын

that would work for you too.

wow

cool

@XcelsiveEnglish

Ай бұрын

Thanks

You can use this to put a live excel table inside other MS programs also.

@XcelsiveEnglish

26 күн бұрын

You can, but it wont update when values are changed in Excel

No need to dig for the camera-icon. Just copy your range and paste it as a Linked Picture.

@XcelsiveEnglish

Ай бұрын

possibly. I haven't tried it myself

Excel has a watch window feature, you could set it to watch your table

@XcelsiveEnglish

Ай бұрын

It does. And it looks terrible!

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

Ай бұрын

Glad that I was able to help. Out of curiosity, what other problems you've faced that could have been solved by this?

@MonkEBoy-ud6kj

Ай бұрын

@@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

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 haven’t tried the camera but copy paste special linked pictures does the same thing. You can cut it into other worksheets.

@analysiscloud

Ай бұрын

@@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.

@XcelsiveEnglish

Ай бұрын

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!

@XcelsiveEnglish

Ай бұрын

Could you explain your method a bit?

@MrSamoDude

Ай бұрын

It sounds like this is probably coming with future Microsoft Loop components.

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.

@XcelsiveEnglish

Ай бұрын

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 😎

@BrianRussell76

Ай бұрын

As it rolls out, PIVOTBY is a great option here too

@rtshort

Ай бұрын

Just saw a video on PIVOTBY just the other day. Waiting for a reason to use it. :)

@XcelsiveEnglish

Ай бұрын

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 kzread.info/dash/bejne/fYSJ0diaotGniKg.html

@XcelsiveEnglish

Ай бұрын

May be this could sway you, kzread.info/dash/bejne/fYSJ0diaotGniKg.html

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.

@XcelsiveEnglish

Ай бұрын

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.

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.

@XcelsiveEnglish

Ай бұрын

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.

@thesexiestguy

Ай бұрын

@@XcelsiveEnglishVBA can move the pivot table as well...based on Selection Change or Change Event...

@XcelsiveEnglish

Ай бұрын

Agreed. But I needed something simple enough that beginners can use too. VBA is a tad bit advanced don't you think?

@thesexiestguy

Ай бұрын

@@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!

@XcelsiveEnglish

Ай бұрын

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.

Data set link sir

@XcelsiveEnglish

Ай бұрын

Here it is tinyurl.com/tybjevpd

Also what is with the channel name??

@XcelsiveEnglish

Ай бұрын

What about it?

Shame it isn't a pane.

@XcelsiveEnglish

Ай бұрын

How do you mean?