*Official* Excel help thread Page 10

  • Page

    of 10 First / Last

    Next
  • X201 18 Jul 2012 09:50:32 15,576 posts
    Seen 22 minutes ago
    Registered 9 years ago
    You can do it in the same cell by repeating you calculation inside the IF statement, but this way gets messy very quickly and a hidden column is the tidier way out.
  • Deleted user 18 July 2012 12:14:35
    Thanks. Discovered the formula they're using in another spreadsheet..

    =IF('Summary incl FY11 O&O vs Aff'!D10<10*1000000;"0-10";IF('Summary incl FY11 O&O vs Aff'!D10<50*1000000;"10-50";IF('Summary incl FY11 O&O vs Aff'!D10<100*1000000;"50-100";IF('Summary incl FY11 O&O vs Aff'!D10<300*1000000;"100-300";IF('Summary incl FY11 O&O vs Aff'!D10<500*1000000;"300-500";IF('Summary incl FY11 O&O vs Aff'!D10<700*1000000;"500-700"))))))
  • Deleted user 18 July 2012 22:16:40
    ps, the *1000000 stuff is because the values were in the millions and they'd truncated them , which threw me for a while.
    Anyway, *1000000 was a stupid way to do it, I managed to refine the formula and it was a happy ending for everyone. :)
  • RyanDS 13 Feb 2013 15:58:07 9,579 posts
    Seen 8 hours ago
    Registered 6 years ago
    So here's one I am struggling with.

    I do a fair bit of reporting in Excel, and I get the raw data through MSquery. Each time I need to run the reports I have to do the following steps:

    Open spreadsheet.
    Click on Data
    Click on Connections
    Double click on the query
    Click Definitions tab
    Click Edit Query.

    (So question one is is there a quicker way of getting to the query wizard?)

    Anyway, that brings up the query wizard. Within that I update the parameters to filter the data. (eg I update the week number to get this weeks figures.) Enter the password and it all updates. Alternately I can amend the SQL script from within connection properties.

    Now all this is easy for me and takes 10 seconds, however when I am away people struggle with these and so I am trying to make a user friendly version. Ideally what I want to do is simply create a cell in the spreadsheet where you enter the week number. Possibly have a big button saying "Press This" and by entering the week into the cell and pressing the button it will automatically change the connection properties and refresh.

    I have tried in the connection properties in the script adding (XXX=Sheet1:A1) instead of XXX=45 (if 45 was the week) but this doesn't work. Is there a way to do this?
  • RyanDS 13 Feb 2013 16:03:39 9,579 posts
    Seen 8 hours ago
    Registered 6 years ago
    Oh, found it.

    For reference I just put ? into the query, and it then allows you to link a cell to that. And it autorefreshes when you change the cell.
  • twelveways 24 May 2013 17:30:08 4,061 posts
    Seen 3 days ago
    Registered 9 years ago
    Hi,
    I was wondering if someone could help me out.

    I want E2 to display something (anything) if A2, B2, C2 or D2 contain a certain string.

    is this possible?

    Thanks :)
  • mrpon 24 May 2013 17:54:15 29,184 posts
    Seen 8 hours ago
    Registered 8 years ago
    Formula for E2

    =IF(A2 <> "","Yes",IF(B2<>"","Yes",IF(C2<>"","Yes",IF(D2<>"","Yes","No" ) ) ) )

    Edited by mrpon at 17:54:52 24-05-2013

    Give yourself 5 or gig, you're worth it.

  • monkehhh 24 May 2013 18:03:14 3,425 posts
    Seen 7 hours ago
    Registered 6 years ago
    =IF(OR(A2="X", B2="X", C2="X", D2="X" ), "Y", "N" )

    =IF(ISNA(MATCH("X", A2: D2, 0)), "N", "Y" )

    (ffffuck, auto-smileys)

    Edited by monkehhh at 18:07:31 24-05-2013
  • twelveways 24 May 2013 19:16:08 4,061 posts
    Seen 3 days ago
    Registered 9 years ago
    Cheers! That helped a lot
  • mrpon 23 Jun 2013 16:23:39 29,184 posts
    Seen 8 hours ago
    Registered 8 years ago
    Totally forgotten this, how do you group by in Excel? For example, column A has:

    ABC
    ABC
    DEF
    GHI

    I want to return the unique values, eg: ABC, DEF & GHI.

    nvm: wrote a macro.

    Edited by mrpon at 17:57:09 23-06-2013

    Give yourself 5 or gig, you're worth it.

  • MrTomFTW Moderator 17 Jun 2014 10:42:13 38,664 posts
    Seen 8 hours ago
    Registered 11 years ago
    I'm *Official*lly looking for help.

    The organisation I work for is expanding, rapidly. And our little 3 man Sys Admin team is stuggling with all the requests. I'm trying to complete all the new account requests, but with the amount coming through it's time consuming.

    As such I'm resorting to scripts instead of manually creating each account in Active Directory, and to help me do that I'm using Excel.

    So in my spreadsheet you enter user details in to columns A-L and it spits out the command line in column O which I can then copy and paste into my CMD window. It works! Great!

    Now I'm being lazy - because I'm doing up to 60(!) accounts at a time I'm copying the contents of column O into a .bat file then running that. What I want to do is make a button in Excel that will automatically output everything in Column O (save for Row 1, which is the header) to a .bat file, overwriting any previous .bat files that this has saved, when I press a button. Then I can just run that file from a shortcut.

    So for example pressing the button would create (or overwrite) runme.bat on my desktop.

    Can this be done in Excel 2007? Does that even make sense?

    Edited by MrTomFTW at 10:43:42 17-06-2014

    Follow me on Twitter: @MrTom
    Voted by the community "Best mod" 2011, 2012 and 2013.

  • MMMarmite 17 Jun 2014 10:50:05 1,064 posts
    Seen 14 hours ago
    Registered 8 years ago
    It can be done but you'll need to create a VBA function to do it and then link that to a button on the worksheet.

    Also you could cut the .bat and create the users directly from Excel providing you have the correct user permissions example script

    Edited by MMMarmite at 10:52:15 17-06-2014
  • MrTomFTW Moderator 17 Jun 2014 10:52:27 38,664 posts
    Seen 8 hours ago
    Registered 11 years ago
    Hmmmmm, that's beyond my knowledge. Or at least the amount of time it would take me to learn how probably won't be worth it.

    edit: I have domain admin permissions, so that script may well work. I shall look into it. Thanks!

    edit 2: It's worth noting I haven't done any of this using VBA. All I've used is the =CONCATENATE function.

    Edited by MrTomFTW at 10:56:03 17-06-2014

    Follow me on Twitter: @MrTom
    Voted by the community "Best mod" 2011, 2012 and 2013.

  • Rodney 17 Jun 2014 11:20:32 1,908 posts
    Seen 43 minutes ago
    Registered 8 years ago
    You don't necessarily need to write VBA to do this, you can just record a macro and record the steps to save/print to file or whatever then assign the macro to a command button.
  • Rodney 17 Jun 2014 11:22:24 1,908 posts
    Seen 43 minutes ago
    Registered 8 years ago
    Recording macros and then viewing the VBA can be an easy way to learn some basic VBA commands. You can normally reverse engineer it to decipher it
  • MMMarmite 17 Jun 2014 11:36:09 1,064 posts
    Seen 14 hours ago
    Registered 8 years ago
    Here's a quick file I knocked up to export the data to a .bat file, it saves "runme.bat" to your default save location (should be My Documents). You can view the code that does the work by clicking Visual Basic in the developer tab.

    ExportO.xlsm

    Edit: it's based on your description of data in columns A to L with the concatenation of those in column O.

    Edited by MMMarmite at 11:37:12 17-06-2014
  • mrpon 17 Jun 2014 11:52:34 29,184 posts
    Seen 8 hours ago
    Registered 8 years ago
    Call me intelligent, but what about copying column O direct into a cmd window?

    Give yourself 5 or gig, you're worth it.

  • MMMarmite 17 Jun 2014 11:55:15 1,064 posts
    Seen 14 hours ago
    Registered 8 years ago
    I suppose you could do that, Mr Intelligent :D
  • MrTomFTW Moderator 17 Jun 2014 12:01:45 38,664 posts
    Seen 8 hours ago
    Registered 11 years ago
    @mrpon I can do that, it does work. I'm just trying to make this as easy and as "foolproof" as possible for anyone else that may use this workbook.

    Enter data here, click button, sip tea, don't bother Tom with questions :D

    Follow me on Twitter: @MrTom
    Voted by the community "Best mod" 2011, 2012 and 2013.

  • mrpon 17 Jun 2014 12:03:25 29,184 posts
    Seen 8 hours ago
    Registered 8 years ago
    Ah users, fair enough!

    Give yourself 5 or gig, you're worth it.

  • MrTomFTW Moderator 17 Jun 2014 12:07:26 38,664 posts
    Seen 8 hours ago
    Registered 11 years ago
    You see, we're gasping for more resources. i.e. people have left us and instead of getting people up here who know what they're doing, they're giving domain admin access to the "best" of the service desk in Wales. So they're "helping" but they keep making mistakes as they pay peanuts down there and get monkeys in return. So not only am I being lazy, I'm also trying to make this as easy as entering a few fields of data and pressing a button :)

    I think I may have found someone here who can help, but I feel I've learnt a bit more about Excel today - this time last week I was new at this whole Excel formula thing!

    Edited by MrTomFTW at 12:08:03 17-06-2014

    Follow me on Twitter: @MrTom
    Voted by the community "Best mod" 2011, 2012 and 2013.

  • X201 17 Jun 2014 12:47:51 15,576 posts
    Seen 22 minutes ago
    Registered 9 years ago
    You can do shedloads with Excel, its not just for maths.

    I do loads of text cutting and reassembly on it.
    I used it to generate the code for a massive table on Wikipedia


    You can get started on VBA by recording macros and editing the code, then just build from there.
  • Page

    of 10 First / Last

    Next
Log in or register to reply