|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.|
*Official* Excel help thread • Page 10
Pageof 14 First / Last
Thanks. Discovered the formula they're using in another spreadsheet..
=IF('Summary incl FY11 O&O vs Aff'!D10
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.
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:
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?
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.
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?
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 4,377 posts
Seen 3 hours ago
Registered 8 years ago
=IF(OR(A2="X", B2="X", C2="X", D2="X" ), "Y", "N" )
=IF(ISNA(MATCH("X", A2: D2, 0)), "N", "Y" )
Edited by monkehhh at 18:07:31 24-05-2013
Cheers! That helped a lot
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
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
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
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
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.
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
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
Call me intelligent, but what about copying column O direct into a cmd window?
I suppose you could do that, Mr Intelligent
@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
Ah users, fair enough!
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
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.
*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 4,770 posts
Seen 28 minutes ago
Registered 15 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 4,863 posts
Seen 9 months ago
Registered 13 years ago
Struggling to understand the issue, can you share the spreadsheet ( without any confidential data obviously ) ?
Ruckly 1,015 posts
Seen 7 hours ago
Registered 2 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 1,116 posts
Seen 1 hour ago
Registered 9 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
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
@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 22.214.171.124 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
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