Видео

Glad you liked it.

I thought so too. And that's why its coming in the next video this friday!!

Thanks for subbing!

Thank you so much 😀

Welcome!

Glad to hear that!

We can. Wait for the next video, it covers file list in ALL the subfolders & folder list too!

simple way would be to add folder location with the file name and extension and use hyperlink with it. Wait for my next video and you can hyperlink those easily since it shows files in sub folders as well and has the folder location too. Folder location coupled with file name with extension when used with HYPERLINK formula does the trick.

Thanks for the suggestion

That is, if you have a multi monitor setup. 💰💰

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

Thank you, I will

possibly. I haven't tried it myself

It does. And it looks terrible!

Here it is tinyurl.com/tybjevpd

How do you mean?

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 ruclips.net/video/KPUovarnAXE/видео.html

May be this could sway you, ruclips.net/video/KPUovarnAXE/видео.html

Glad you enjoyed it! Don't forget to subscribe and share!

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

Glad it helped!

Glad it was helpful!

Glad I could help!

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

Thanks for watching!

What about it?

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.

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

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.

Thanks

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.

Thanks

Yes you can! For week numbers, you can try WEEKNUM formula. For the national holiday part, the only way i can think of is to prepare a list separately and then use that.

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 😄

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.

Thanks for the support

Unfortunately, not yet. Zatca QR codes must have specific data such as seller's name, VAT registration number which is possible, but there are many more fields like Hash of XML electronic invoice, Cryptographic Stamp, Public Key etc, which is not available with Excel, Yet. You can find options to create it online though

My pleasure!

My pleasure 😊

You're welcome

Glad it was helpful!

Could be. But you have to use power query first to get them all together and then use pivot. Using power pivot skips this altogether