Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

nellie23's Avatar
nellie23 nellie23 is offline
Member with 34 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Beginner
20-Jan-2009, 06: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's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
20-Jan-2009, 06: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.
Rollin_Again's Avatar
Member with 4,699 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
20-Jan-2009, 08: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's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
21-Jan-2009, 02: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's Avatar
nellie23 nellie23 is offline
Member with 34 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Beginner
21-Jan-2009, 03: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's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
21-Jan-2009, 04: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's Avatar
nellie23 nellie23 is offline
Member with 34 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Beginner
21-Jan-2009, 11:04 AM #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.

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's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
21-Jan-2009, 01: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's Avatar
nellie23 nellie23 is offline
Member with 34 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Beginner
22-Jan-2009, 04: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
File Type: xls excel formula mock up.xls (28.0 KB, 224 views)
Albantar's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
22-Jan-2009, 05: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?
Rollin_Again's Avatar
Member with 4,699 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
22-Jan-2009, 08: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's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
22-Jan-2009, 09: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's Avatar
nellie23 nellie23 is offline
Member with 34 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Beginner
23-Jan-2009, 08: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
File Type: xls excel formula mock up.xls (28.5 KB, 124 views)
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
23-Jan-2009, 09:33 AM #14
I think Rollin_Again's formula works perfectly for what you've asked above. Here it is implemented.
Attached Files
File Type: xls excel formula mock up-1.xls (40.0 KB, 294 views)
Albantar's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
23-Jan-2009, 10:04 AM #15
Hi Nellie,

does this look like what you need?
Attached Files
File Type: xls excel formula mock up (1).xls (55.0 KB, 324 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑