|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
X201 14,586 posts
Seen 9 hours ago
Registered 8 years ago
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"))))))
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?
monkehhh 3,065 posts
Seen 9 hours ago
Registered 5 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