Results 1 to 7 of 7

Thread: Still confused on where to start

  1. #1
    Neophyte Kalmarjan's Avatar
    Join Date
    Nov 2021
    Posts
    4
    Articles
    0
    Excel Version
    Office 365

    Still confused on where to start



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,726
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    I can't upload an example because of privacy and corporate concerns,
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte Kalmarjan's Avatar
    Join Date
    Nov 2021
    Posts
    4
    Articles
    0
    Excel Version
    Office 365
    Oh boy.

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

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,893
    Articles
    0
    Excel Version
    O365
    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.

  5. #5
    Neophyte Kalmarjan's Avatar
    Join Date
    Nov 2021
    Posts
    4
    Articles
    0
    Excel Version
    Office 365
    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...Click image for larger version. 

Name:	ClickHere.PNG 
Views:	10 
Size:	4.5 KB 
ID:	10719Click image for larger version. 

Name:	Result.PNG 
Views:	9 
Size:	8.0 KB 
ID:	10720

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,893
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Kalmarjan View Post
    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...
    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?

  7. #7
    Neophyte Kalmarjan's Avatar
    Join Date
    Nov 2021
    Posts
    4
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by Bob Phillips View Post
    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?
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •