Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 75 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 9:43 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Yup - that works a treat.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 9:47 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Oh, huh - and the vlookup works today, too. Didn't yesterday.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 10:11 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
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)


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 10:38 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 11:28 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 11:36 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Well damn.

Hopefully that's just for 2003, though?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 16:44 
User avatar

Joined: 30th Mar, 2008
Posts: 14130
Location: Shropshire, UK
Pretty sure I constantly run in to the same issue on 2010 and 2013.

I fucking hate VLOOKUP as a result.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 14:39 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:02 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:05 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:09 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
No. That isn't returning the last populated cell.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:11 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
It does in Excel 2013. Is their other odd stuff going on that you're not telling us about?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:13 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Wait - are all the cells in that row populated?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:15 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:17 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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!

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:16 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
How can i get excel to let me have one sheet per screen? It tiles sheets in one window.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:18 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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)?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:23 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:24 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:26 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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).

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:27 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:29 
User avatar
Excellent Member

Joined: 25th Jul, 2010
Posts: 11128
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:33 
User avatar

Joined: 12th Apr, 2008
Posts: 17757
Location: Oxford
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:34 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Sep 28, 2015 16:39 
User avatar
Ready for action

Joined: 9th Mar, 2009
Posts: 8542
Location: Top Secret Bunker
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?


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Sep 28, 2015 16:42 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Sep 28, 2015 16:49 
User avatar
Ready for action

Joined: 9th Mar, 2009
Posts: 8542
Location: Top Secret Bunker
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Sep 28, 2015 16:51 
User avatar
Ready for action

Joined: 9th Mar, 2009
Posts: 8542
Location: Top Secret Bunker
I'm an idiot. Ignore me. I think that worked and now I love you.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Sep 28, 2015 16:51 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
What the fuck was wrong with me before?!

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Sep 28, 2015 17:01 
User avatar
Ready for action

Joined: 9th Mar, 2009
Posts: 8542
Location: Top Secret Bunker
Grim... wrote:
What the fuck was wrong with me before?!

:kiss:


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue Sep 29, 2015 7:37 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38439
Grim... wrote:
What the fuck was wrong with me before?!

Nothing, but now you're excel-lent


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue Sep 29, 2015 7:41 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
I have the formula for success!

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue Sep 29, 2015 9:45 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38439
You're A1


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 10:49 
User avatar
Bad Girl

Joined: 20th Apr, 2008
Posts: 14353
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


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 11:09 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Gah, compact view makes my eyes melt!

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 11:11 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
[–]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.

:D

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 11:25 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Ha! Brilliant.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 11:28 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 12:17 
User avatar

Joined: 27th Mar, 2008
Posts: 25549
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.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 12:53 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38439
The mac office suite is (subjectively) awful.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Nov 05, 2015 13:04 
User avatar

Joined: 27th Mar, 2008
Posts: 25549
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).

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 14, 2016 15:29 
User avatar

Joined: 12th Apr, 2008
Posts: 17757
Location: Oxford
Today I learnt about =BAHTTEXT(number) which converts numbers to Thai and adds 'baht'. This now replaces =ROMAN(number) as my favourite formula.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Jan 09, 2017 15:17 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
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)),"")}

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Jan 09, 2017 15:18 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
It's a reference to an array of cells instead of a single cell?

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Mon Jan 09, 2017 15:20 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
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.

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 75 posts ]  Go to page 1, 2  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: Columbo, Squirt, Vogons and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen. RIP, MrC.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.