Excel Help

Discussion in 'Off-Topic Chat' started by andywooler, Feb 7, 2007.

  1. andywooler

    andywooler Supporting Member

    Totally non banding help needed!

    I need to copy a formula in Excell from one cell into 300. However, part of the formula needs to remain constant whilst part increments by one in each line - here's the problem:

    In Column A, we have a text description.
    In Column B we need to work out the equivalent code for that text.

    I am using lookup to do this rather than a load of nested IF statements as I have more than 7 possibilities!

    The options are listed elsewhere in the tabe in columns f2:f30 with corresponding codes in g2:g30

    Here's the problem: The formula currently reads =LOOKUP(A2,f2:f30,g2:g30)

    when in the appropriate cell in line 3, it needs to read:

    =LOOKUP(A3,f2:f30,g2:g30) and for line 4 =LOOKUP(A4,f2:f30,g2:g30)

    If I use paste then all of the numbers increment and I get

    =LOOKUP(A3,f3:f31,g3:g31) and so on.

    Any excel geniuses out there who have any tips will be rewarded with beer when we next meet at a contest!
  2. DaveR

    DaveR Active Member

    My solution would be to write a perl script to do it and then import it back into Excel!! Much easier! But I guess you have to know perl (although it's not difficult to learn enough to do this quite quickly)....

    Sorry Andy, not much help to you on this one.
  3. Well Worth It

    Well Worth It Active Member

    This may take some trial and error as I can't quite remember which.....but if you enclose the "fixed" data in certain symbols.....I THINK it's $, but it may be & or something else.....
    I think it's called fixed cell referencing....

    i.e. $column$row
    Try that!

    Last edited: Feb 7, 2007
  4. andywooler

    andywooler Supporting Member

    Spot on - I owe you one!!
  5. TheMusicMan

    TheMusicMan tMP Founder Staff Member

    Yup, that's the thing to do...

    Excel has relative and absolute references. You need the absolute reference which, as Well Worth It correctly stated, is applied by prefixing the area of the cell formulae with the $ character.
  6. Di B

    Di B Member

    As an addition to this, you can also then just freeze either the column or row reference by using $columnrow or column$row

    Another helpful hint is actually to use excels help file. It is actually quite good at showing where the common excel problems are - I still use it now for certain formula types (the formula LEFT is simple but soooo useful in my job!!)
  7. mikelyons

    mikelyons Supporting Member

    Why do I always miss these questions!?

    Another helpful tip is to select the part of the formula you want to be absolute (fixed) and press F4. This will automatically put in the $ signs for you.

    another way you might try is to name the fixed ranges (select the cells then type the name in the place on the top left of the main sheet. (It is a box and has a name, but senility is setting in!)
  8. andywooler

    andywooler Supporting Member

    It was actually easier and quicker to ask on here! Indeed, prior to posting on tMp, I also used my usual web resources. tMp got me there the quickest.
    (I did start with the help file and found it as is often the case, somewhat difficult to find the answer).
  9. TheMusicMan

    TheMusicMan tMP Founder Staff Member

    Haha... nice one.... I have often done the same thing Andy and like you, have received helpful and accurate advice on here from tMP'ers before receiving advice from elsewhere.

    I must point out a superb site though... http://forums.techguy.org/ - A superb resource indeed.
  10. timbloke

    timbloke Member

    I know the feeling.

    But what's interesting (or not perhaps), is that whilst I'd consider myself a bit of an Excel genius, I've never used (known how to or why to) the LOOKUP function. Yet the $ sign bit is something I always use. Strange how you can always learn something new.
  11. andywooler

    andywooler Supporting Member

    However, whilst I now know how to fix the way I create the formula, Lookup doesn't work for me in the way I want it to - and before anyone says read the help file, that has no help at all on this function unless you plan to use it for finding values (I'm seeking out text and each time you change the order of the array thay you are looking up, the anser changes!!)
  12. stuartw

    stuartw Member

    I think what you want for your formula is the following


    THe VLOOKUP function tells it that your data are in columns. The first bit is the cell you want to check, the second range contains all the data ie the parent values and your decoded values starting at the top left and ending at the bottom right and it has the $ signs to be invariant. The 3rd argument is the column from which you want to take the value which as it is in the second column of the range (F is the first , G is the second) has the value 2. The last argument is FALSE and you need this to make it do the correct thing if it doesnt find the answer - I never really understood this bit but it needs to be FALSE most of the time!

    I think this should work

  13. andywooler

    andywooler Supporting Member

    Stuart, that did indeed work - thanks to all who contributed here - I now know about both lookup and vlookup!
    It is the FALSE at the end which cured the glitches.
  14. mikelyons

    mikelyons Supporting Member

    Even more weird is that, while I have used vlookup on a number of occasions, I've never been able to get hlookup to work properly. I just don't like working in rows, I suppose. I even fill tables in column-wise.

    VLOOKUP is useful for looking values up if you store them in a table (or better still a named range) in another part of the sheet. You can store a set of values off-screen and then use the vlookup to find them when a user inputs a value. Saves having to keep typing the values into formulae.