08-04-2015, 10:44 AM
|
#1
|
First Line Centre
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 122 202 203 207 208 216 217 219 221 222 224 225 313 317 HC G
|
Excel questions thread
I see through search we have plenty of individual question threads and bumping one of them didn't seem like the best option. So I've created this general question thread for Excel noobs like myself.
My issue is date formatting. I have a .xls file at work, on Excel 2010, Windows 7. When I bring it home to my Excel 2013, Windows 7, I can not get date formatting to work. On the spreadsheet dates are entered as 08/04/2015. When I enter new dates at home in Excel 2013, it keeps changing to 8/4/2015.
I'm guessing the issue is in my regional settings for Windows 7? I've gone through all of those, making sure I selected mm/dd/yyyy, changing from US to Canada, etc. But for the life of me I still cant get Excel to recognize those zeros. Any ideas? Searching online has been a headache as well, just cant find my specific issue anywhere.
|
|
|
08-04-2015, 11:31 AM
|
#2
|
Franchise Player
|
When you go to format cells, how do you see the date? If I have mine set to "English(Canada)" I get 14/03/2012. Changing to English(United States) I get I get 3/14/2012. Now, this will only function on those cells in that sheet. Excel uses system defaults, which you should be able to change in the language control panel applet.
|
|
|
The Following User Says Thank You to Fuzz For This Useful Post:
|
|
08-04-2015, 01:43 PM
|
#3
|
First Line Centre
|
What happens if you just insert a column and use:
=TEXT(value,"dd/mm/yyyy")
Yeah it converts it to a string but does it preserve the formatting?
|
|
|
08-04-2015, 02:37 PM
|
#4
|
First Line Centre
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 122 202 203 207 208 216 217 219 221 222 224 225 313 317 HC G
|
I think I was able to solve it by selecting the column and formatting it with the date format. So I guess I'm overriding the default format of a particular PC? So no matter what PC I open it with, it will format correctly each time.
|
|
|
08-04-2015, 02:40 PM
|
#5
|
Franchise Player
|
Ya, and if you go to the control panel I mentioned, you should be able to set the default format for both PC's to be the same.
|
|
|
The Following User Says Thank You to Fuzz For This Useful Post:
|
|
06-01-2016, 04:24 PM
|
#6
|
First Line Centre
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 122 202 203 207 208 216 217 219 221 222 224 225 313 317 HC G
|
So another Excel question pops up on CP! Any help would be greatly appreciated. I have this formula:
=COUNTIFS(RAW_DATA!D:D,{"Yellow","Brown","Green"}, RAW_DATA!G:G,"<15000",RAW_DATA!P:P,{"Apple","Pear" ,"Cherry"})
This formula creates no errors so it appears to be working. I want to count the rows that have yellow, brown or green in column D, below 15000 in column G (including zeros) and is also an apple, pear or cherry in column P. However the calculation is not accurate. This formula will generate say a count of 415. But if I go into the sheet and use Filters and manually count, I get 625. Any ideas?
|
|
|
06-02-2016, 08:07 AM
|
#7
|
Franchise Player
|
did you press ctrl-shift-enter after modifying the formula? It is an array, so you have to do that.
|
|
|
The Following User Says Thank You to Fuzz For This Useful Post:
|
|
06-02-2016, 09:24 AM
|
#8
|
First Line Centre
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 122 202 203 207 208 216 217 219 221 222 224 225 313 317 HC G
|
Yeah I dont have much experience with arrays. I did that and it added the {} brackets to the entire formula, no change. I may have to spend some time learning them
|
|
|
06-02-2016, 10:01 AM
|
#9
|
Franchise Player
|
"Apple","Pear" ,"Cherry"
There is a space after "Pear" try removing that.
|
|
|
07-19-2016, 01:59 PM
|
#10
|
Lifetime In Suspension
|
Any idea why inputting a formula would only work via that stupid formula button instead of just manually typing it in? One damn company's report I can no longer do Vlookups manually, dives me nuts. As soon as I try to select the range it tells me there's too few arguments. They must have changed some format but damned if I can figure it out. Any ideas from my crap explanation?
|
|
|
07-19-2016, 02:04 PM
|
#11
|
Franchise Player
|
is it an array? You need to press "shift-enter" to activate it.
|
|
|
07-19-2016, 02:15 PM
|
#12
|
Lifetime In Suspension
|
...shift and enter you say? What sorcery is this. Goddammit now I feel stupid.
No, that didn't fix it I don't think. Odd. I don't know what the hell
So like this is what happens. I do =vlookup(A2,alt/tab to my other sheet, A2 again and hold down shift, go over two rows to what I want, and as soon as I I go to hit the down arrow and ctrl along with shift it reacts like I've hit enter to end my formula.
That explanation was probably terrible.
Last edited by ResAlien; 07-19-2016 at 02:21 PM.
|
|
|
07-19-2016, 02:31 PM
|
#13
|
Franchise Player
|
Hrmm, weird. Have you tried re-booting? The only thing I would suspect is the sheet you are doing a vlookup on had the the row outside of what is expected, so if you press shft-ctrl-down on it it doesn't stop at your last data entry, it goes way below. You could test doing that outside a formula, just to see. Of course, you should be able to manually enter the formula but who wants to type all that, eh?
|
|
|
07-19-2016, 02:33 PM
|
#14
|
Lifetime In Suspension
|
Bah there is no god. This adds like 80 second to my workday for one function. Unreasonabl. It's only when I hit ctrl that it gdoes stupid. Guess I'll send this off to Robert Stack.
Last edited by ResAlien; 07-19-2016 at 02:36 PM.
|
|
|
07-19-2016, 02:40 PM
|
#15
|
Franchise Player
|
You could try calling Microsoft tech support. *snickers*
|
|
|
The Following User Says Thank You to Fuzz For This Useful Post:
|
|
07-19-2016, 02:43 PM
|
#16
|
First Line Centre
|
Quote:
Originally Posted by ResAlien
Bah there is no god. This adds like 80 second to my workday for one function. Unreasonabl. It's only when I hit ctrl that it gdoes stupid. Guess I'll send this off to Robert Stack.
|
My guess is that you are tabbing to a spreadsheet that is opened in a different 'instance' of Excel. (sort of like, you have opened excel twice and the sheets are in each separate Excel). If you close the one book, and then open it from the same excel (file -> open) when you are staring at your first book then your formula clicking should work.
|
|
|
07-19-2016, 02:49 PM
|
#17
|
In the Sin Bin
|
Quote:
Originally Posted by ResAlien
...shift and enter you say? What sorcery is this. Goddammit now I feel stupid.
No, that didn't fix it I don't think. Odd. I don't know what the hell
So like this is what happens. I do =vlookup(A2,alt/tab to my other sheet, A2 again and hold down shift, go over two rows to what I want, and as soon as I I go to hit the down arrow and ctrl along with shift it reacts like I've hit enter to end my formula.
That explanation was probably terrible.
|
Are you sure it's catching you change to the other sheet? So does it show =vlookup(A2,Sheet2!A:X,etc...)?
|
|
|
07-19-2016, 02:53 PM
|
#18
|
Lifetime In Suspension
|
^tried that, didn't resolve it. Something about the worksheet coming from the other company is making the ctrl button do crazy things. Guess I'll just use page down instead. Goddam odd though, it's only this one report from one person and she swears she's changed nothing. She's obviously devious and up to something sinister. Thanks for trying to help, guys. I do appreciate it.
Yes Polak, I'm sure. Every other way of doing it works just fine, just the ctrl button acts like enter. Hence my confusion
I KNOW IT'S A WOLF, OK? IT COULDN'T POSSIBLY BE A DOG!
Last edited by ResAlien; 07-19-2016 at 02:58 PM.
|
|
|
07-20-2016, 02:14 PM
|
#19
|
First Line Centre
Join Date: Oct 2010
Location: Deep South
|
Could it be that the VLOOKUP is looking for the true/false argument at the end when you are not using the formula wizard? I think if you use the wizard then it defaults to false, but maybe it's not smart enough to do that when you just type it in without the wizard?
Likely more possible if that is an older version of a spreadsheet being used in an updated version of excel.
__________________
Much like a sports ticker, you may feel obligated to read this
|
|
|
11-23-2016, 05:02 PM
|
#20
|
First Line Centre
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 122 202 203 207 208 216 217 219 221 222 224 225 313 317 HC G
|
So I'm back with another question for the CP braintrust, any help would be appreciated. I have this spreadsheet:
The green section is fairly straight forward, I have to distribute files every day to a group of six staff. Today I am distributing 1000 files (D2). Of these six staff, Shannon and Greg are slackers, so they only get 10.6% of the files, everyone else is even at 19.7% (A5:A10).
The yellow section is where it gets tricky. Some staff are here a full day and some are half days and some call in sick to play Battlefield 1 (come on Terry...). So the question is what would my formula look like in the red section (H5:H10) so that regardless of who shows up to work all 1000 files get assigned?
I think I'm asking for a lot here, I'm assuming the formula might be fairly long
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -6. The time now is 04:24 AM.
|
|