1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Can you drag formula in excel so the numbers increase?

Discussion in 'Business Applications' started by nellie23, Jan 20, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. nellie23

    nellie23 Thread Starter

    Joined:
    May 23, 2008
    Messages:
    34
    It's hard to come up with a snappy title that sums up my problem but I'm hoping it will be fairly simple to solve.

    I've created an IF formula in an excel spreadsheet and I want to drag the cell across a few more cells. The formula will reference the same cell so I can make that an 'absolute reference' but then I want the logical test number to increase by 1 each time and the value if true to remain the same.

    =IF($A$1>0,1) [in the first cell, then in the next to the right]
    =IF($A$1>1,1) [then]
    =IF($A$1>2,1) [and so on]

    Can anyone please tell me if this is possible? and if so, how??!!

    heres hoping,
    Nellie.
     
  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    The only way I can think to solve this is to reference the increments to cells ( that increment) in column alongside the formula that you drag down HTH.
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    I agree that a helper column should be used with the number reference being obtained from the helper column. The only other way I think this can be done is using a macro. If you decide to go the macro route just let me know and I can help with the code.

    Regards,
    Rollin
     
  4. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    You can do this with the function COLUMN(), which returns the column number of the current cell (1 for column A, 2 for column B, etc).

    So if you start in column A and drag the formula right, your formula would be:

    =IF($A$1>COLUMN()-1;1)
     
  5. nellie23

    nellie23 Thread Starter

    Joined:
    May 23, 2008
    Messages:
    34
    thanks for the responses so far, but can you (any of you!) go into a bit more detail with your suggestions as I'm still only at the basics stage of formulas.

    Turbodante/Rollin - can you please elaborate on how to set up a helper column? can you give an example?


    Albantar - the formula you quoted doesn't run when I try it. Do I need to enter some extra info in the brackets? It just returns the error message at the moment.


    thanks
    Nellie
     
  6. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    O, sorry nellie, you must be using US or UK locales...

    Try replacing the semicolon in the formula by a comma.

    In Continental European locales, such as the Dutch one that I use, the comma is used as decimal symbol, so it can not be used to separate arguments in formulas. In UK and US locales the period is the decimal symbol, and commas are used to separate arguments... A continental EU formula looks like =FUNCTION(ARG1;ARG2;ARG3;...), while a US/UK formula looks like =FUNCTION(ARG1,ARG2,ARG3,...), thus the confusion...
     
  7. nellie23

    nellie23 Thread Starter

    Joined:
    May 23, 2008
    Messages:
    34
    Albantar,

    UK is correct.

    I've made the change to the formula and it is now acceptable. But when I drag it using the fill handle it just repeats the formula rather than increasing the logical test number.
    I have a feeling that it maybe me being clueless.

    the formula now reads:
    =IF($A$1>COLUMN()-1,1)

    but it only returns with FALSE regardless of what is in the absolute cell.

    Should there be some reference between the brackets that appear after "COLUMN"

    Also, if it does work, I need to adapt it as the starting cell is not in column A


    fingers crossed,
    Nellie
     
  8. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    Hi Nellie,

    could you please attach your Excel file to the message board so I can have a look at the actual functions as you put them in place?

    (Of course the file should not contain any private information; you could attach a mockup instead with fake data...)
     
  9. nellie23

    nellie23 Thread Starter

    Joined:
    May 23, 2008
    Messages:
    34
    Albantar,

    I have hopefully attached a reduced version of the file (there is nothing on the post screen to suggest it is attached!)

    the A numbers that appear in column A in sheet 1 should match the same numbers in sheet 2.
    I have left the formula, I want to appear, in for A024 - row 11 cells B to K, which references the quantity in sheet 2 cell E28. But I would like to drag/paste them in so I don't have to type the formula in every cell I need to.

    The ten cells to the right of each cell displaying an A number should reference the corresponding cell in sheet 2 column E.

    I hope this goes some way to explaining what I'm after.

    cheers,
    Nellie.
     

    Attached Files:

  10. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    Yeah, I got the file, but there are no headers to the columns so it's a bit hard to figure out what you wish to do. What is the ultimate goal of this sheat?
     
  11. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Just use the following Formula in cell B11 and copy across through column K.

    =IF(Sheet2!$E$28>COLUMN()-2,1)

    Regards,
    Rollin
     
  12. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    Hmmmm, that still doesn't quite answer my question though...

    What do the different rows/columns on the datasheets mean, and what is the ultimate goal; as in, what information do you wish to obtain with the formula? What's the description of the problem you're trying to solve with it?
     
  13. nellie23

    nellie23 Thread Starter

    Joined:
    May 23, 2008
    Messages:
    34
    thanks for the ongoing support...
    the formula suggested still does not increase the 'value if true' as I drag it along.

    I've added some simple headings to try and explain what I'm after.

    Imagine if the rows called A002 to A006 & A024 on sheet 2 were different fruits. For every fruit Ii buy I add the corresponding quantity in the Qty Complete column. So A024 currently shows 8 plums in stock.
    I want sheet 1 and its corresponding row to input a '1' for every fruit salad a plum is available for.
    The formula I created will return a 1 in sheet1 B11 if sheet2 E28 has number greater than 0. return a 1 in sht1 C11 if sht2 E28 has a number greater then 1 and so on.

    I would have to type this in by hand (as far as I'm aware) but my actual spreadsheet is pretty big and would like to be able to drag across.

    clear as mud, right?

    still hoping,
    Nellie
     

    Attached Files:

  14. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I think Rollin_Again's formula works perfectly for what you've asked above. Here it is implemented.
     

    Attached Files:

  15. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    Hi Nellie,

    does this look like what you need?
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/792378