[Closed] Just learnt a powerful new Excel technique

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

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

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, [u]none[/u] 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.

[img] [/img]

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 & [u]even edit[/u],

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

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

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).

[b]=SUMIFS(B1:B10,A1:A10,"AB*")[/b] 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 [u]any[/u] 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.

[url= http://www.ozgrid.com/VBA/ ]VBA/Excel'ist help [/url]
[url= http://www.mrexcel.com/archive/Formulas/index.html ]Excel'ist help guru[/url]

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 [url= http://forums.cgsociety.org/forumdisplay.php?f=98 ]mine[/url]

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.

BTW just found that this will work across different sheets quite happily.

i.e. Criteria range on one sheet, value range on another.

I know, not that remarkable, but good to know.

grr, anyone know why I cant get this SUMPRODUCT to work with OFFSET properly?
Just getting #VALUE

My OFFSET formula is valid on its own, and the SUMPRODUCT formula is valid with the range entered as a full address. But swap the range address for the OFFSET statement and it no longer works...

you can also use a simple SUM function, but with an IF statement emnedded in it, and entered as an array function.

Press CTRL + Shift + Enter to complete the formula:

{=SUM(IF(LEFT(A1:A10,LEN(F1))=F1,B1:B10))}

emergency people!

It appears that INDIRECT wont work with Dynamic Ranges.

i.e. OFFSET((INDIRECT($B$29&"$K$10")),0,0,270,1) throws up a #VALUE when used in a SUMPRODUCT formula.

The reason Im using INDIRECT is to be able to build a formula that can address specific worksheets.

Anyone got any ideas which function will bring back a range but can be built up from a text input for the Sheetname?

INDEX I dont think will work.

I had quite a time trying to resolve (I think) this very issue some years ago. I wonder what I did...

How about using the offset as a named formula?

named formula or named range?

if named range, Id need to create a named range for each incidence (50cols by time, bringing back SUMPRODUCTS from 20odd worksheets)

http://spreadsheetpage.com/index.php/oddity/a_range_name_is_really_a_named_formula/

Cheers Rob, will digest later. Off to plumbing school now...

Like no way dude - parallel lives!

I'm doing level 2 on Mondays & Wednesday evenings.
Get the feeling you're a bit ahead of me.

You don't also have flashbacks to when you were a small Amish girl churning butter on a rainy day do you?*

*Made this bit up.

Rob Hilton - Member
Like no way dude - parallel lives!

I'm doing level 2 on Mondays & Wednesday evenings.
Get the feeling you're a bit ahead of me.

no, not ahead of you Rob. I decided to start from scratch so am 3/6 terms into 6129 City & Guilds first, before L2 and L3. I decided that although I have quite a bit of experience I have some holes in my basic knowledge and I might as well fill them on the way through. Im in no rush.

Tuesday night practicals and Wednesday theory. Hands as black as soot ATM from mucking about with LCS pipe.

Maybe an interest in Excel leads to a propensity to plumb. After all, CH is just AND & OR gates is it not? 😉