*Official* Excel help thread Page 10

  • X201 18 Jul 2012 09:50:32 18,461 posts
    Seen 9 hours ago
    Registered 12 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
  • 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 11,782 posts
    Seen 8 hours ago
    Registered 10 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 11,782 posts
    Seen 8 hours ago
    Registered 10 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 7,131 posts
    Seen 1 month ago
    Registered 12 years ago
    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 34,303 posts
    Seen 8 hours ago
    Registered 11 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
  • monkehhh 24 May 2013 18:03:14 4,819 posts
    Seen 4 hours ago
    Registered 10 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 7,131 posts
    Seen 1 month ago
    Registered 12 years ago
    Cheers! That helped a lot
  • mrpon 23 Jun 2013 16:23:39 34,303 posts
    Seen 8 hours ago
    Registered 11 years ago
    Totally forgotten this, how do you group by in Excel? For example, column A has:


    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
  • MrTomFTW Best Moderator, 2016 17 Jun 2014 10:42:13 47,491 posts
    Seen 5 hours ago
    Registered 15 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
  • MMMarmite 17 Jun 2014 10:50:05 1,562 posts
    Seen 1 hour ago
    Registered 11 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 Best Moderator, 2016 17 Jun 2014 10:52:27 47,491 posts
    Seen 5 hours ago
    Registered 15 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
  • Rodney 17 Jun 2014 11:20:32 3,500 posts
    Seen 1 week ago
    Registered 11 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 3,500 posts
    Seen 1 week ago
    Registered 11 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,562 posts
    Seen 1 hour ago
    Registered 11 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.


    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 34,303 posts
    Seen 8 hours ago
    Registered 11 years ago
    Call me intelligent, but what about copying column O direct into a cmd window?
  • MMMarmite 17 Jun 2014 11:55:15 1,562 posts
    Seen 1 hour ago
    Registered 11 years ago
    I suppose you could do that, Mr Intelligent :D
  • MrTomFTW Best Moderator, 2016 17 Jun 2014 12:01:45 47,491 posts
    Seen 5 hours ago
    Registered 15 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
  • mrpon 17 Jun 2014 12:03:25 34,303 posts
    Seen 8 hours ago
    Registered 11 years ago
    Ah users, fair enough!
  • MrTomFTW Best Moderator, 2016 17 Jun 2014 12:07:26 47,491 posts
    Seen 5 hours ago
    Registered 15 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
  • X201 17 Jun 2014 12:47:51 18,461 posts
    Seen 9 hours ago
    Registered 12 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.
  • Deleted user 16 July 2015 15:24:33
    *AHHHHHHH* can someone please help me.

    I am creating a new management accounts spreadsheet at work and have built myself into a bit of a corner.

    I have created a spreadsheet which looks up certain cells when someone selects a department from a drop down box.

    Then the user can select a month and it will give you the information from that cell for that date. This all works well but i now need to calculate the cumulative figure and i cannot work out how to use the formula to sum over a period of time.

    my original formula are;

    To calculate the cell reference

    =IF($A$7="Payroll",'Data Sheet'!A219,IF($A$7="Marketing",'Data Sheet'!A269," "))

    to pick up the data for the month

    =IF($A10=" "," ",VLOOKUP($A10,'Data Sheet'!$A$5:GS294,B$5+1,FALSE))

    anyone able to help?
  • crashVoodoo 16 Jul 2015 19:17:52 5,738 posts
    Seen 6 hours ago
    Registered 16 years ago
    Are you wanting to get the sum of data from a column if data in other columns matches what's selected in the drop downs?

    Edited by crashVoodoo at 19:19:19 16-07-2015
  • Clive_Dunn 16 Jul 2015 19:31:00 4,862 posts
    Seen 3 months ago
    Registered 15 years ago
    Struggling to understand the issue, can you share the spreadsheet ( without any confidential data obviously ) ?
  • Ruckly 31 Jul 2015 11:18:04 1,066 posts
    Seen 2 years ago
    Registered 3 years ago
    I'm having a shed collapse.

    Using an email package called constant contacts. It has a feature that will let you export all the contacts that opened/did not open an email you sent out in a campaign to an excel sheet.

    So let's say I have five sheets of these contacts for each campaign - each sheet will have all the contacts that opened, and they may or may not be on the other sheets as well. I want to be able to filter this along the lines of did they open two or more of the recent emails; if so we'll keep them on the list. I basically just want a way of seeing who our regular openers are and then binning the rest.

    Constant contacts are sneaky bastards and they keep people on the list because they get paid by the email; even if people are never opening. I want to see who is actually regularly opening our stuff.

    Anyway, the question relates to filtering across multiple sheets, seeing who's on maybe 1, 2, 3 or 4 of them and seeing who's just on one.

    Is there also a way of filtering with conditions - so they must have opened the latest one and one more - obviously it's no good to me if they opened our campaigns from 2 years ago but have stopped opening.

    I hope this makes sense; any excel spotters who can help me out I will love you forever.
  • basmans_grob 31 Jul 2015 11:50:18 1,469 posts
    Seen 10 hours ago
    Registered 10 years ago
    Have a summary sheet with every name on it. You can easily create one by cutting and pasting the names from every sheet underneath each other then remove duplicates. Then in a cell next to the name have =countif(range of people who opened email one, name), in the next cell countif(range of people who opened email two, name)etc. The countif will return a value of one for each of the time they have opened an email.

    You can then total up the column to get a number of 0 to 4. You can then pivot, or filter, on the last email and see who also read the other ones.

    Edited by basmans_grob at 11:50:38 31-07-2015
  • X201 31 Jul 2015 11:54:38 18,461 posts
    Seen 9 hours ago
    Registered 12 years ago
    Off the top of my head...

    You'll need a master list, copy all the email addresses to a master sheet, and use the Remove duplicates command on it.

    Then you need to do a Lookup/Match for each mailshot in a separate column on the master list.

    Then you can just add a "Total" column to do a countif to see how many they've opened

    You can also add the AND into the selection criteria for the match, but I prefer to build things separate first and then merge into more complex formulas when I've proved it works.

    Edited by X201 at 11:56:49 31-07-2015
  • Deleted user 31 July 2015 13:29:31
    @Clive_Dunn hey sorry for the delayed response,

    i actually managed to fix the answer using

    =IF(A14=" "," ",IF($A$7="Payroll",SUMIFS(Budget!222:222,Budget!$3:$3,"="&Y$5),IF($A$7="Marketing",SUMIFS(Budget!272:272,Budget!$3:$3,"="&Y$5)," ")))

    i have in the next tab, they select a date the system works out what number the column is and then they search for dates between the most recent may (start of the financial year) and the month selected.

    it will be a pain to update but works out quite well
  • Deleted user 31 July 2015 13:32:06
    i would like to point out that i am pretty much self taught on excel and until about 9 months ago my excel skills went no further than =sum() so i may not make much sense
Log in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.