Search Search for: Business ApplicationsAll Forums

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

 nellie23
Member with 34 posts.

Join Date: May 2008
Experience: Beginner
20-Jan-2009, 07:01 AM #1
Solved: Can you drag formula in excel so the numbers increase?
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
Senior Member with 744 posts.

Join Date: Dec 2008
Location: GMT UK
20-Jan-2009, 07:33 AM #2
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.
Member with 4,693 posts.

Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
20-Jan-2009, 09:49 AM #3
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
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
21-Jan-2009, 03:28 AM #4
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
Member with 34 posts.

Join Date: May 2008
Experience: Beginner
21-Jan-2009, 04:36 AM #5
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
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
21-Jan-2009, 05:03 AM #6
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
Member with 34 posts.

Join Date: May 2008
Experience: Beginner
21-Jan-2009, 12:04 PM #7
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.

=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
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
21-Jan-2009, 02:26 PM #8
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
Member with 34 posts.

Join Date: May 2008
Experience: Beginner
22-Jan-2009, 05:40 AM #9
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
 excel formula mock up.xls (28.0 KB, 206 views)
 Albantar
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
22-Jan-2009, 06:08 AM #10
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?
Member with 4,693 posts.

Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
22-Jan-2009, 09:50 AM #11
Just use the following Formula in cell B11 and copy across through column K.

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

Regards,
Rollin
 Albantar
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
22-Jan-2009, 10:30 AM #12
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
Member with 34 posts.

Join Date: May 2008
Experience: Beginner
23-Jan-2009, 09:28 AM #13
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
 excel formula mock up.xls (28.5 KB, 113 views)
 turbodante
Senior Member with 744 posts.

Join Date: Dec 2008
Location: GMT UK
23-Jan-2009, 10:33 AM #14
I think Rollin_Again's formula works perfectly for what you've asked above. Here it is implemented.
Attached Files
 excel formula mock up-1.xls (40.0 KB, 282 views)
 Albantar
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
23-Jan-2009, 11:04 AM #15
Hi Nellie,

does this look like what you need?
Attached Files
 excel formula mock up (1).xls (55.0 KB, 313 views)
 techguy.org/792378
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)