I'm trying to copy/paste cells that have the following formula (using OpenOffice but I assume Excel is similar):

=LOOKUP(B13;Resources.A2:A6;Resources.B2:B6)

So basically I have a worksheet 'resources' which has two columns A (which is a name) and B (which is a number).

I have another worksheet which looks up a value in 'Resources' based on what is in a cell (B13 in this case) and prints what it finds in the Resources worksheet.

I have a stack of these rows (B13, B14, B15, B16, etc). I want to copy/paste the cell this formula is in.

It magically detects that it's changing cells, so it updates the formula to correctly also have B13, B14, B15, etc in it - but it also incorrectly updates the Resources columns (to being Resources.A3:A7, Resources A4:A7, etc).

Is this just how copy/paste works? I feel like I'm doing something fundamentally wrong here, but maybe you can't just magically copy/paste stuff like this.

I can obviously write out the formula correctly in each cell, but it seems that I'm just doing something wrong.

IN excel you'd use a $ sign, I'll assume its the same

=LOOKUP(B13;Resources.$A$2:$A$6;Resources.$B$2:$B$6)

I'd also imagine there is a named range function.

*edit* Insert -> names -> define

last edited by Obes at 18:34:09 12/Jul/07

i dont have a copy of excel in front of me but yes u can lock cells so that they dont change when another cell changes from a copy

from memory you want to make the cells absolute cells if you dont want them to change on a copy/paste

so if open office doesnt use dollar signs look up how to make the cell absolute.. it might help

Legend, thanks heaps. Now I just need to figure out why one lookup works for one row, and one doesnt :|

Try using countif() to double check the value exists (ie. is exactly the same)

I avoid lookup most of the time, I'd prefer to use vlookup. But that's just me.

Obes, you got OO installed?

aha: Additionally, the search vector for the LOOKUP must be sorted, otherwise the search will not return any usable results.

Now I gets it

right click, paste special - values only ?

but yeah the $ will hold the cell as well.

like others have said it's to do with relative and absolute references. In Excel, A dollar sign in front of a Range indicates it is absolute, so won't change when you copy the formula elsewhere. You have individual control over the column and row reference - eg. $A$1 is totally absolute, $A1 - only the row number will change when copied, A$1 - only the column will change.

In Excel you ca just hit F4 after selecting or entering the range and it will cycle that range through the various absolute reference settings.

Heardy is a noob ... again ...

He didn't want to paste the values, he wanted a formula with some absolute references, so he could copy and paste them.

Work mentioning that you can just make one part of the reference absolute.
For instance you might have multiple columns that represent seperate cost centers and you want to see what each cost center spent for the year, as well as monthly cash flows. Assume Column A, and row 1 have titles in em. In B14 put =sum(B$2:B$13) ... then fill right and you have annual cost center costs. Assume there is data out to column N. In O2, put =sum($B2:$N2) and fill down. Done. And if you copy and paste those 2 rows or columns to other another row or column they'll maintain their integrity. ps. trivial example.

ps. There is a much much better way to do this by keeping data more "relational" and using Data Pilot / Pivot tables.

And while absolute references do the trick, a named range is better, so that if down the track you had to edit the looked up values (ie. add or remove data). You only have to change the named range once, instead of however many thousands of times you used that range in a formula.

So here's my new problem - I have a table like this: http://trog.qgl.org/up/halp.jpg

What I want to do is what I believe in Excel is called a "conditional sum" - basically I want to have a cell (say D13) which contains the value of:

IF (A="BLAH") AND (B=10) THEN SUM (corresponding field in C)

This sounds like an utterly, utterly simple thing to do, but appears complicated enough that Excel has a whole separate wizard for it. I found a formula like this but I have not yet been able to get it to work, and it seems counter-intuitive to me so I'm not sure if it's the "official" way of doing it.

You can do it easily if you're willing to insert an extra column like so:

http://members.iinet.net.au/~m.holmes/misc/xl1.PNG

Basically set values to zero if they don't meet the condition, else preserve the value. Then sum up this new set of values in D5.

orb, yeh, I thought of that - except these things are getting printed on a different worksheet.

I have got a bunch of different workarounds that I can use, but I am stubbornly trying to make this work because I spent about 2 hours last night reading documentation and Google. It seems SO SIMPLE so I can't believe its this hard.

I tried Obes' DataPilot thing and it works pretty well except I can't do what I want and have these values on this different worksheet (Basically I'm trying to make a "report" type page and I want it to be as simple as possible).

do what orbit says and then hide the C colum once you are finished?

fair enough then, here is the single cell solution for my example:

=SUM(((A1:A4="blah")*(B1:B4=10))*C1:C4)

Note you MUST enter the formula as an Array function. This is done with CTRL+SHIFT+ENTER in Excel.

last edited by orbitor at 09:35:12 13/Jul/07

holy s***, I just pressed CSE on the formula that I had and BAM, it worked straight away. What the f***ing f*** is an array function?!!?@#?!@#?!@?#!@?#?@!#?!@

An Array formula just does a defined operation on a bunch of values in turn (with the values being in the array). eg. it'll use all the first elements of the arrays (ranges) in the formula, then all the 2nd, etc. Then perform the overall function on the array of results.

If you have the "Evaluate Formula" button use that and you'll see straight away what it does.

my brain hurts...

Is another way of doing array functions to put { and } around them?

that doesn't work in excel, it decides it's a text string and not a formula if you actually type the curly brackets.

hmm, I asked because when I use CSE, it puts curly brackets around the formula in the formula bar thing. Maybe its an OO thing.

yeah it does that in Excel as well, but entering the curly brackets yourself doesn't seem to be recognised.

ahh ok right.

Thanks heaps dude, I feel like an a****** though for not figuring this out. 2 hours where I could have just pressed CSE and it would have worked perfectly!

I obviously need a newb guide to spreadsheets

This thread just reminds me of how much i hate excel, and how much more i am going to hate it when i start my new job :(

This thread just reminds me of how much i hate excel, and how much more i am going to hate it when i start my new job :(

Why? It's all very logical. You just figure out what process you'd use to do something manually, then try and throw it all together in a formula. If it's a real toughy, you start by breaking into steps in different cells and then putting it together later.

Trog: don't worry, if you didnt' know about Array formulae it was always going to be a tough ask. It's like programming hey, you do something the mega-tough-roundabout way then someone goes "oh there's this nice library/function that does all that for you"...