Still confused on where to start

Good morning, I'll try to keep it brief.

I've been using Excel on and off for a little over 20 years. I mostly used it for keeping track of recipes in a kitchen (I used to be a chef) and for entering/tracking inventory data. I knew a bit of conditional formatting, how to roughly lookup items with a VLookup table, etc.

I am an avid Photoshop user, so as such I am a person that LOVES keyboard shortcuts, and Excel is amazing for that.

Fast forward to today. I no longer work in kitchens, and I now work at a corporation in the planning department. There are a LOT of uses for Excel here, and my day-to-day job entails working with data from SAP, and manipulating that data with Excel to make reports. I sometimes need to rely on National to make reports before I can continue in my workflow, which is something I know I can't change.

What I would like to do is further my knowledge in Excel, but there so much information out there. I have NO idea where to start.

I work with Pivot tables all day, there are slicers everywhere, and sometimes I feel like there are things that I am doing (like hitting buttons on slicers to get values to enter into another table) that I could automate, but where does a chap start? I can't upload an example because of privacy and corporate concerns, but the gist is something like this:

There are 40 installations that all provide service, and take X numbers of hours to do so. I have an excel sheet that has a pivot table that I press each slicer to filter down the installation, and that pops out a number that I have to enter into another excel file to give me a graph. I have a feeling I could just somehow link the two and save some time? (Hopefully not make me obsolete lol)

This is an example, there are many things I would like to learn, and I am a person that is pretty hands on in learning...

Thank you for reading my ramble, I'm lost and need some direction LOL

I can't upload an example because of privacy and corporate concerns,

Click to expand...

No, and we would not expect you to.

Pleae prepare a small, desensitised copy that shows the issues you are facing and has manual mock-ups of what you want to achieve.

Oh boy.

That's where I am stuck, I am not sure about where to even begin with that. ;/

Are you aware of the macro recorder, tried recording an example of the steps that you take to see what the code looks like?

If you did that and posted it, we could improve it (the macro recorder is overly verbose) and maybe suggest how to extend it in a way that you could manage.

Okay, I see what you mean.
I have used the macro recorder in the past, I am very, very new when it comes to VBA.

I was looking in the back end of the pivot tables I use, and there are 4 different connections I use in one, An OLEP query, 2 ACCESS OLE DB queries, and another one that looks like it was made with a Power Pivot add-in (it says "Model OLE DB Query) the latter is something I think someone else made for their department, as it is exactly what I would like to accomplish Lol

If I double click on a value in the pivot table, it will open up a new table with all the data that is attached to it, so I think I am on the right track...

Kalmarjan said:

If I double click on a value in the pivot table, it will open up a new table with all the data that is attached to it, so I think I am on the right track...

Click to expand...

Not sure how that puts you on the right track, that is standard pivot table behaviour.

Where are you looking at for those connections? PowerPivot connections are to ThisDatamodel.

So are you good to take the next step?

Bob Phillips said:

Not sure how that puts you on the right track, that is standard pivot table behaviour.

Where are you looking at for those connections? PowerPivot connections are to ThisDatamodel.

So are you good to take the next step?

Click to expand...

Right now I am reading up on Pivot tables and how they work. I am good at formatting excel files and making them look pretty, some light VLookup functionality, but this is completely new for me.

An example, I get a file that has a bunch of data linked to it as a pivot table with a bunch of slicers. I have to click the slicers to get the amount for one out of say, 7 zones. Then I need to put that value in another file. I just don't know how to just look up the value in the pivot table file and have it automatically put the value where I need it. Right now when I do my report, I need to get a total of 32 different values. so that is 32 different clicks. It takes me around 30 minutes to make this report, and I think I may be able to just pull the data from where I need it by referencing the file itself. I also forgot to mention that all of these files are also on SharePoint, and links come from there.

I don't know if I am being silly on this, but I was thinking there has to be a way, and I am willing to get there, I just don't know which direction to start from.