|
Dec 2019
12:42pm, 10 Dec 2019
3,365 posts
|
Merry Fizzmas :-)
I've been forwarded an invite to an Excel XLOOKUP webinar - "Key takeaways include XLOOKUP's ability to return all 12 months in a single formula, which is exciting enough to make the INDEX/MATCH people switch" . . .
|
|
Dec 2019
12:53pm, 10 Dec 2019
116,739 posts
|
GregP
Wowser
|
|
Dec 2019
12:57pm, 10 Dec 2019
1,019 posts
|
Nessie
I got that invitation too.
|
|
Dec 2019
12:44pm, 11 Dec 2019
309 posts
|
icenutter
That is more exciting than it has any right to be.
|
|
Jan 2020
6:05pm, 7 Jan 2020
4,435 posts
|
quimby
Can I be cheeky and ask something I could *probably* work out with a fair bit of Googling, but I think you lot might smash it a lot faster? Single Excel workbook (2013 if it matters), multiple sheets, each has a list of dates and account balances (financial). Just imagine those two columns (there are more, but irrelevant for my purpose here). At the top of the sheet, I want a cell that displays just the current balance, ie the formula needs to look down the date column, find the most recent date, and display the balance in the next column over. With the ultimate aim of summing them on another sheet. Thanks in advance.
|
|
Jan 2020
6:41pm, 7 Jan 2020
112 posts
|
Neal
Back to INDEX and MATCH.
If the column of dates is called "dates" and the two colomns together are called "date_bals",
=INDEX(date_bals,MATCH(MAX(dates),dates,0),2)
gives the right sort of thing.
|
|
Jan 2020
4:53pm, 8 Jan 2020
4,436 posts
|
quimby
Ta very much Neal. Wasn't expecting it to be quite so simple! 
|
|
Jan 2020
5:47pm, 8 Jan 2020
4,437 posts
|
quimby
Curiously, I set the ranges on the first worksheet to be worksheet-specific. Then I copied the formula to another sheet with exactly the same layout and Excel created new worksheet-specific ranges on that one. Magic! Tried it on a third worksheet and it created workbook-specific ranges, which of course nuggered up the fourth paste. Then on the fifth (after deleting the workbook-specific ones), it worked again? Anyway, all set up now. Cheers!
|
|
Jan 2020
3:52pm, 24 Jan 2020
1,846 posts
|
Oranj
Completely confused myself this afternoon by accidentally selecting-all before 'hide'. Cue an invisible worksheet and 5 minutes blind panic until I realised that no, I hadn't deleted all the 2019 data I'd been carefully curating.
|
|
Jan 2020
3:35pm, 29 Jan 2020
44,616 posts
|
Diogenes
I really enjoy using Excel to do clever things. I'm not that smart myself but I'm fairly good at using Google to borrow other people's intelligence. I've also learnt (and forgotten) a lot from this thread.
However, I find that it's all very well using advanced functions and features but, if the people you are sharing workbooks with don't understand them they aren't of much use. I also get people not trusting Excel and doing manual cross-checks. Anyone else get this?
|
