Grim... wrote:
What the fuck was wrong with me before?!
| Be Excellent To Each Other https://www.beexcellenttoeachother.com/forum/ |
|
| Excel Help https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=9857 |
Page 1 of 2 |
| Author: | Cras [ Wed May 20, 2015 9:43 ] |
| Post subject: | Re: Excel Help |
Yup - that works a treat. |
|
| Author: | Cras [ Wed May 20, 2015 9:47 ] |
| Post subject: | Re: Excel Help |
Oh, huh - and the vlookup works today, too. Didn't yesterday. |
|
| Author: | Bobbyaro [ Wed May 20, 2015 10:11 ] |
| Post subject: | Re: Excel Help |
if you sorted the list to make mine work, it may have made the vlookup work, vlookup sometimes doesn't work on unsorted lists (stupidly) |
|
| Author: | Grim... [ Wed May 20, 2015 10:38 ] |
| Post subject: | Re: Excel Help |
Bobbyaro wrote: if you sorted the list to make mine work, it may have made the vlookup work, vlookup sometimes doesn't work on unsorted lists (stupidly) Wait, what?! I know sorting them makes it (far) faster, but I can't see how it would stop it working at all. That's the whole point of it, surely? |
|
| Author: | Bobbyaro [ Wed May 20, 2015 11:28 ] |
| Post subject: | Re: Excel Help |
https://support.microsoft.com/en-us/kb/181212 Quote: LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order. The following information describes different formulas that you can use to return the same information returned by LOOKUP without requiring that the first column of the table be sorted.
|
|
| Author: | Grim... [ Wed May 20, 2015 11:36 ] |
| Post subject: | Re: Excel Help |
Well damn. Hopefully that's just for 2003, though? |
|
| Author: | GazChap [ Wed May 20, 2015 16:44 ] |
| Post subject: | Re: Excel Help |
Pretty sure I constantly run in to the same issue on 2010 and 2013. I fucking hate VLOOKUP as a result. |
|
| Author: | MaliA [ Fri May 29, 2015 14:39 ] |
| Post subject: | Re: Excel Help |
in cell I6 I can select the last cell in the row using "=lookup(1E+100,B6:DW6)" to pull back last week's sales data. In the next cell,J6,I want to then return the value 52 columns before that (same week, last year). So going to the last populated cell, then counting back. I am struggling with this one. |
|
| Author: | Grim... [ Fri May 29, 2015 15:02 ] |
| Post subject: | Re: Excel Help |
Code: =INDEX(A6:ZZ6,COUNT(A6:ZZ6)) will get you the last value in row 6, so you want Code: =INDEX(A6:ZZ6,COUNT(A6:ZZ6)-52) Edge case: Some years have 53 weeks - make sure that doesn't muck up your result. |
|
| Author: | MaliA [ Fri May 29, 2015 15:05 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: Code: =INDEX(A6:ZZ6,COUNT(A6:ZZ6)) will get you the last value in row 6, so you want Code: =INDEX(A6:ZZ6,COUNT(A6:ZZ6)-52) Edge case: Some years have 53 weeks - make sure that doesn't muck up your result. Thank you. |
|
| Author: | MaliA [ Fri May 29, 2015 15:09 ] |
| Post subject: | Re: Excel Help |
No. That isn't returning the last populated cell. |
|
| Author: | Grim... [ Fri May 29, 2015 15:11 ] |
| Post subject: | Re: Excel Help |
It does in Excel 2013. Is their other odd stuff going on that you're not telling us about? |
|
| Author: | Grim... [ Fri May 29, 2015 15:13 ] |
| Post subject: | Re: Excel Help |
Wait - are all the cells in that row populated? |
|
| Author: | MaliA [ Fri May 29, 2015 15:15 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: It does in Excel 2013. Is their other odd stuff going on that you're not telling us about? No,I was being daft. it works, thank you. It was pulling a zero value due to a vlookup being on the end. I have adjusted it now. Thank you. Much obliged. |
|
| Author: | Grim... [ Fri May 29, 2015 15:17 ] |
| Post subject: | Re: Excel Help |
Or - d'uh - if it's not populated with number then swap COUNT for COUNTA. I'm not sure why I thought they would all be numbers, to be honest. [edit] Hurrah! |
|
| Author: | MaliA [ Thu Aug 06, 2015 10:16 ] |
| Post subject: | Re: Excel Help |
How can i get excel to let me have one sheet per screen? It tiles sheets in one window. |
|
| Author: | Grim... [ Thu Aug 06, 2015 10:18 ] |
| Post subject: | Re: Excel Help |
What Office version are you on? If you're on 2013 you want View | New Window. Also, I take it you mean two sheets from the same file (if not, just open a new Excel instance by shift-left clicking on the Excel icon)? |
|
| Author: | TheVision [ Thu Aug 06, 2015 10:23 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: (if not, just open a new Excel instance by shift-left clicking on the Excel icon)? Woah! I didn't know this! Great work. |
|
| Author: | MaliA [ Thu Aug 06, 2015 10:24 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: What Office version are you on? If you're on 2013 you want View | New Window. Also, I take it you mean two sheets from the same file (if not, just open a new Excel instance by shift-left clicking on the Excel icon)? I want file A on monitor A and file b on monitor b. Excel 2007 |
|
| Author: | Grim... [ Thu Aug 06, 2015 10:26 ] |
| Post subject: | Re: Excel Help |
Just open a new instance of Excel, either by holding shift down while you open it, or by clicking on it with the middle mouse button (sometimes). |
|
| Author: | MaliA [ Thu Aug 06, 2015 10:27 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: Just open a new instance of Excel, either by holding shift down while you open it, or by clicking on it with the middle mouse button (sometimes). Lovely, thank you. |
|
| Author: | Bamba [ Thu Aug 06, 2015 10:29 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: Just open a new instance of Excel, either by holding shift down while you open it, or by clicking on it with the middle mouse button (sometimes). I can do this just by launching Excel twice from the Start Menu, though I'm on Office 2010 in case that makes a difference. |
|
| Author: | Kern [ Thu Aug 06, 2015 10:33 ] |
| Post subject: | Re: Excel Help |
It's sometimes easy to get confused over whether a sheet is open in the same session or not. I found that some tools won't work across different instances of the program when trying to share data/formuale. |
|
| Author: | Grim... [ Thu Aug 06, 2015 10:34 ] |
| Post subject: | Re: Excel Help |
Bamba wrote: Grim... wrote: Just open a new instance of Excel, either by holding shift down while you open it, or by clicking on it with the middle mouse button (sometimes). I can do this just by launching Excel twice from the Start Menu, though I'm on Office 2010 in case that makes a difference. It does indeed. |
|
| Author: | sdg [ Mon Sep 28, 2015 16:39 ] |
| Post subject: | Re: Excel Help |
Help please! I have a sheet with list of products by product code which run on a particular production line. I have another sheet with the full list of products we make on site, with columns of components associated with each product. How do I sort the second sheet to only show me the products on the first sheet? |
|
| Author: | Grim... [ Mon Sep 28, 2015 16:42 ] |
| Post subject: | Re: Excel Help |
Simplest is probably something like: Code: =IF(COUNTIF(Sheet1!A:A,Sheet2!A1)=0,"No","Yes") Will show you if whatever is in A1 on Sheet 2 exists in the column A of Sheet 1. Then you filter by the Yes/No column. |
|
| Author: | sdg [ Mon Sep 28, 2015 16:49 ] |
| Post subject: | Re: Excel Help |
I'm not sure what's not worked about that but it says yes for the first two rows then says no for all others. I should have 115 matches. |
|
| Author: | sdg [ Mon Sep 28, 2015 16:51 ] |
| Post subject: | Re: Excel Help |
I'm an idiot. Ignore me. I think that worked and now I love you. |
|
| Author: | Grim... [ Mon Sep 28, 2015 16:51 ] |
| Post subject: | Re: Excel Help |
What the fuck was wrong with me before?! |
|
| Author: | sdg [ Mon Sep 28, 2015 17:01 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: What the fuck was wrong with me before?!
|
|
| Author: | DavPaz [ Tue Sep 29, 2015 7:37 ] |
| Post subject: | Re: Excel Help |
Grim... wrote: What the fuck was wrong with me before?! Nothing, but now you're excel-lent |
|
| Author: | Grim... [ Tue Sep 29, 2015 7:41 ] |
| Post subject: | Re: Excel Help |
I have the formula for success! |
|
| Author: | DavPaz [ Tue Sep 29, 2015 9:45 ] |
| Post subject: | Re: Excel Help |
You're A1 |
|
| Author: | Satsuma [ Thu Nov 05, 2015 10:49 ] |
| Post subject: | Re: Excel Help |
I'll just hung this in here as it has the word Excel in the thread title. I don't know if anyone saw or is even remotely interested in this but the Microsoft Excel team did an AMA on Reddit. https://www.reddit.com/r/IAmA/comments/ ... g/.compact |
|
| Author: | Grim... [ Thu Nov 05, 2015 11:09 ] |
| Post subject: | Re: Excel Help |
Gah, compact view makes my eyes melt! |
|
| Author: | Grim... [ Thu Nov 05, 2015 11:11 ] |
| Post subject: | Re: Excel Help |
[–]mdcsd 119 points 15 hours ago Do you guys ever fight with the Word or PowerPoint guys? [–]MicrosoftExcelTeam [S] 822 points 15 hours ago Sometimes but they tend to be verbose and have no data to support their position. 
|
|
| Author: | Cras [ Thu Nov 05, 2015 11:25 ] |
| Post subject: | Re: Excel Help |
Ha! Brilliant. |
|
| Author: | Cras [ Thu Nov 05, 2015 11:28 ] |
| Post subject: | Re: Excel Help |
Quote: What does your accounting department think of your work?
MicrosoftExcelTeam[S] 714 points 16 hours ago Our finance guys are big users of Excel and periodically come by to show us the cool stuff they've done. - Howie genuinecve 1004 points 15 hours ago Is that like being a parent and having your kid show you a finger painting and acting like it was done by Van Gogh? ThouArtNaught 544 points 14 hours ago I like to believe they print out their spreadsheets and stick them on the office fridge. |
|
| Author: | Mimi [ Thu Nov 05, 2015 12:17 ] |
| Post subject: | Re: Excel Help |
Hahaha! That's brill. Incidentally, I moved up to Excel 2016 and it is lovely. I think I had 2013 at work, but only (Mac) 2008 at home, and 2008 was evil. I hated using it. 2016 is pretty pretty. |
|
| Author: | DavPaz [ Thu Nov 05, 2015 12:53 ] |
| Post subject: | Re: Excel Help |
The mac office suite is (subjectively) awful. |
|
| Author: | Mimi [ Thu Nov 05, 2015 13:04 ] |
| Post subject: | Re: Excel Help |
Well, I guess that depends on the version you are using. The Office 2016 suite I have now is Mac, and I think it's much nicer than the 2013(? - Russell?) that we have at work (PC version). |
|
| Author: | Kern [ Thu Jan 14, 2016 15:29 ] |
| Post subject: | Re: Excel Help |
Today I learnt about =BAHTTEXT(number) which converts numbers to Thai and adds 'baht'. This now replaces =ROMAN(number) as my favourite formula. |
|
| Author: | Bobbyaro [ Mon Jan 09, 2017 15:17 ] |
| Post subject: | Re: Excel Help |
What does the {4;5} do in this formula? {=IF(C8="Job complete",(D8+E8)-SUM(VLOOKUP(B8&" - Job assigned/passed",$A$1:$E$70,{4;5},0)),"")} |
|
| Author: | Mr Russell [ Mon Jan 09, 2017 15:18 ] |
| Post subject: | Re: Excel Help |
It's a reference to an array of cells instead of a single cell? |
|
| Author: | Mr Russell [ Mon Jan 09, 2017 15:20 ] |
| Post subject: | Re: Excel Help |
Because it's supposed to be the column number in the range containing the return value you want, I guess you are wanting two values back as part of this array formula, and so it is returning two columns back. |
|
| Page 1 of 2 | All times are UTC [ DST ] |
| Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |
|