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



## nellie23 (May 23, 2008)

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.


----------



## turbodante (Dec 19, 2008)

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.


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## Albantar (Dec 9, 2008)

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)


----------



## nellie23 (May 23, 2008)

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


----------



## Albantar (Dec 9, 2008)

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


----------



## nellie23 (May 23, 2008)

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


----------



## Albantar (Dec 9, 2008)

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


----------



## nellie23 (May 23, 2008)

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.


----------



## Albantar (Dec 9, 2008)

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?


----------



## Rollin_Again (Sep 4, 2003)

Just use the following Formula in cell B11 and copy across through column K.

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

Regards,
Rollin


----------



## Albantar (Dec 9, 2008)

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?


----------



## nellie23 (May 23, 2008)

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


----------



## turbodante (Dec 19, 2008)

I think Rollin_Again's formula works perfectly for what you've asked above. Here it is implemented.


----------



## Albantar (Dec 9, 2008)

Hi Nellie,

does this look like what you need?


----------



## nellie23 (May 23, 2008)

Thanks for all the hard work.

Maybe I was pasting Rollin's formula in the wrong place, it does work as in the example turbodante sent as does Albantars.

If at first you don't succeed, use fruit salad!!

cheers
Nellie

Solved!


----------



## Cpl. Chronic (Feb 15, 2008)

Try these tips sites:

http://www.contextures.com/tiptech.html
http://excel.tips.net/
http://www.exceltip.com/

I don't know all that much about formulas in excell but I've gotten some good
info from these sites.


----------



## nellie23 (May 23, 2008)

Sorry, 
one small easy to solve problem.

the example sent by turbodante worked fine. I went in to sheet 2 and changed the number in sheet 2 E28. sheet 1 row 11 reflected it exactly as I wanted.
I then pasted it into another spreadsheet (for safety I even picked the row that would use sht 2 E28 as the referenced cell) and it wouldn't work!

typing 1 - 5 only returned false. It took a number 6 to return a 1 and then only in the first box, the subsequent boxes increase by 1 as they should.

Just had a play and realised it's because I'm starting my formula in column G not B as per the mock up.
Can the formula be adapted to start in column G??

I know you can do it.

Nellie


----------



## nellie23 (May 23, 2008)

bump!!

I mistakenly marked this as solved before encountering another problem.

please see previous post.

thanks 
Nellie


----------



## Albantar (Dec 9, 2008)

Hi Nellie,

The key to fixing this is understanding how the formula works.

In turbodante's sheet the formula in B11 reads:

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

Now think of what you would like that formula to do in that cell...

_If E28 is greater than 0, the result should be 1_

This works because COLUMN() in row B returns 2. To make it zero, you substract 2. Now if you drag this formula to row C, COLUMN() will return 3, and 3-2 is 1, so the formula will return 1 if the value in E28 is greater than 1, etc.

So now lets say you start off in column G. What do you want the formula in, for example, G11, to do?

Exactly, you want it to return 1 if the target value is greater than... zero.

What does COLUMN() return when you're in column G?

So how much do you need to substract from COLUMN() to make the formula work?

The rest is left as exercise 

By the way, if you use $E28 instead of $E$28 in the formula, you can also drag the formula down and up; whereas currently you need to manually modify the first cell in each line and then drag them across the line, by removing that one dollar sign you could do by only fixing the formula in the top-left-most cell, then drag it across the line, and then drag that line down across the entire table.


----------



## nellie23 (May 23, 2008)

Thanks for the extra help.

After a little playing I worked it out.

And thanks for the info regarding dragging up and down, very helpful.

Solved - definitely this time 

Cheers 
Nellie


----------

