|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 10 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 3,897 posts
Seen 8 hours ago
Registered 7 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.
Grax 4,208 posts
Seen 1 hour ago
Registered 2 years ago
*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,214 posts
Seen 9 hours ago
Registered 14 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,840 posts
Seen 37 minutes ago
Registered 12 years ago
Struggling to understand the issue, can you share the spreadsheet ( without any confidential data obviously ) ?