Just learnt a powerful new Excel technique

*notes those posting for when he is in power*

Cynical realises that you cant hope to run the world without the help of Excel geeks.

7 of 9. We have…met, vividly, in my teens…..i hope it wasn’t in my twenties.

she and I have “assimilated” on a number of occasions…

I only ever use VBA code now and just do everything in that as it’s just as quick once you’re fluent and it has better maintainability and documentability than formulas in cells.

shame this place [/url]doesn’t have a forum, you’d fit right in Stoner.

I think you would befit from learning python or something, impressive little trick using excel stoner but it just screams wrong tool for the job!

d’ja miss the bit when I said it was a module for viewing an existing Excel model with?

But full marks for the traditional STW approach to a thread…want a new steel hardtail? I recommend a Santa Cruz Blur!

😉

Clever. Will have to see if I can use that. Chances are, not. But I still like it.

Powerful might be overstating it, though.

Wot footflaps said.

OK clever cloggses, excel me this:

how do I make excel look at text in 2 adjacent cells and then if both fit a defined pattern, change the contents of one of those cells. I’m going to macro the bugger after that and save myself about 5 hours a month

|manually titting about is OK|stoner’s excel macro tips are 20 x better|

becomes

|manually titting about is OK|20 x less menial crap|

but

|working it out for yourself will never happen|stoner’s excel macro tips are 20 x better|

stays as it is

Sumproduct function is truly the wonder of Excel, back in the good old days when people wanted to pay me with twiddling about with it I used to use it all the time.

This is the place to learn it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Bob is pretty much recognised as the world expert when it comes to Sumproduct.

I hate VBA. I really only ever work in functions (which is what that ^ up there is) unless I need to run a macro.

UDFs are so handy sometimes (& powerful!); VBA can make things so much tidier.

cheers for the link Rob, will go and explore.

As for UDF’s my problem is my models have to be used (and to a certain extent be understood) by the client and UDF’s or reams of VBA behind a sheet just isnt going to cut it. The objective has always been to make the models slim and efficient using all the standard, documented, functions available.

Scaredypants,

how do I make excel look at text in 2 adjacent cells and then if both fit a defined pattern

define your pattern and I can have a look at it for you.

what rubbish, try this:

1. Open Excel 2000.
2. Go to File, Save as Web Page.
3, Then click Publish.
4. Check the checkbox that says “Add Interactively With”.
5. Click Publish.
6. Close Excel.
7. Open IE.
8. Click File, Open, Browse.
9. Go to where you saved it and click open.
10.Now you should be looking at a spreadsheet.
11. Go to all the way to row 2000.
12. Click column A and drag all the way to column ZZ.
13. Hit Tab and it should take you back to A.
14. Hold Tab all the way to column WC. (Press shift Tab in case you move back).
15. When you get there make sure WC is a white box and all the others are blue or green..
16. Hold Ctrl+alt+shift and left click on the “four puzzle pieces” thing (The
MS Excel Logo) on the upper left hand corner..

This is the place to learn it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Nice link Rob – that explains it all very well

define your pattern and I can have a look at it for you

Cheers, but no pattern, this is a large database of descriptive terms that should use the 2nd column to categorise them but this has been done inconsistently without any logic at all – consequently there are multiple instances of “cross-categorisation” that I currently have to clean up manually line by line
(I’m imagining a glorified search & replace function but can’t see how I could do it)
🙁
yeah, 1stworldwhining – I know

I’m betting there was a secretary involved somewhere along the line with that, eh scaredy 😉

nope, a supposedly qualified person 🙄 (we’re all just muddling through, none of us does IT and what I’m trying to do wasn’t envisaged when they started with this crap)

Stoner, I’ve said it before and I’ll say it again, I can see how you get all the girls.

slim and efficient using all the standard, documented, functions available.

Non sequitur

If you’re using Sumproduct you’ve got a client who can’t follow what you’re doing anyway 🙂 Bits of code can make it all so much smoother and, even, easier for the client to follow & even edit, rather than formulae that rely on helper cells etc. Not least because you can add notes to the code.

There’s sooooo much you can do in VBA to make your models slick that you can’t use the front end for – go on, do it! You know you want to!

for the client to follow & even edit,

haha! Comedian! 😆

Anyway, this old dog isnt going to be learning any VBA tricks any time soon.

That link is great Rob, dont know why it never turned up on my google searches in the past. bookmarked now.

This is the place to learn it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Well timed link from Rob. Just had a quick glance and it definitely looks interesting. Bookmarked.
Stoner: keep ’em coming 🙂

haha! Comedian!

I’m seriously! Some people even appreciate that you view them as potentially competant and start to delve into the mysteries themselves.

Give a man a fish etc.

Anyhoo… try putting “Ozgrid” in your search text – most of the answers are in there somewhere.

Give a man a fish etc.

And give him a rod and he’ll #REF for the rest of his life 🙂

Ive found some stuff off Ozgrid, but sometimes it’s tricky trying to pick through some of the crappier user posts that clutter up otherwise useful threads.

tricky trying to pick through some of the crappier user posts that clutter up otherwise useful threads

Just like STW then 😉

Hmmm, sometimes not knowing too much about spreadsheets could be a good thing as it saves you getting to tied up with complexities (I haven’t learn sumproduct yet).

=SUMIFS(B1:B10,A1:A10,”AB*”) works for me (Excel even highlights the parts of the criteria range matched by the wildcard) 😮

^ New in 07 (I’m still stuck in 05 🙂 ) Sumifs does the job nicely, but isn’t as flexible as Sumproduct (which can be used for really unexpected things).

The database functions can also be used for such type things, but I never bothered to learn them.

Wow, do you guys know how to party or WHAT!

😉

^

It’s either this or arguing with TJ.

Or getting on with what I’m sposed to be doing…

SUMIFS is a handy addition to 2010 and fills some of the functional gaps in SUMIF. But while it works for search AB* etc, I was using it as an illustration for using any logical test and in any array as a SUM criteria, which SUMIFS cant handle.

Wow, do you guys know how to party or WHAT!

Mine’s a diet coke and a taxi home by 9pm please 😉

SUMIFS is a handy addition to 2010

*Ahem*

New in 07

Rob needs to leave the glowing screen alone for a while

All help with knowledge share…

These guys we use quite regularly and encouraged.

VBA/Excel’ist help [/url]
Excel’ist help guru

New in 07

Hey, Ive only just moved up from 2003! Im still suffering culture shock!
🙂

3 pages on Excel!!! it’s not often posts reach double figures.

as this is a flaunt your geek week, heres one of mine

is the whole forum yours or were you trying to link to a specific post?

is the whole forum yours or were you trying to link to a specific post?

I think he was demonstrating his ability to include links in his text within a forum

Cheers Stoner, just saved me some time and no doubt more in the future with that formula.

good to hear. Didnt realise there might be a backlog of demand for the “Double Unary” (–) ! 😉

Ive just sat down to use the formula in earnest now. Lets hope it holds up ….

I am loving the XmlImport method in VBA at the moment enabling me to pull in loads of data from the web manipulate it and re publish it.

VBA is where its at there is only so much you can do with functions.