Just learnt a powerful new Excel technique

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? 😉

Let the sediment build up sediment and you could be looking at a NOT 🙂

God knows how but I’ve passed my level 1 and I haven’t even touched any LCS yet! Some disorganisation going on at my college…

I haven’t even touched any LCS

I wouldnt be too worried. ‘orrible stuff. We have to cut our own threads by hand – no automatic dieing machines in our college! Doing an assessment piece at the moment – hanging two rads – with two step overs. Its unforgiving stuff.

have tried lots of workarounds and just cant get offset and indirect to work together in this formula. Im going to have to do direct formula 🙁

It’s defnly do-able, but I’m a bit too rusty at this stuff now to give an answer without wasting lots of time

ach. Its done w directs now. Deadline looming in 48 hrs – sometimes you just have to crack on and damn the finesse.

If I find a workaround once the model’s done Ill drop it in later.