[nellie23]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

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

Regards,
Rollin

[Albantar]

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]

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]

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

[Albantar]

Hi Nellie,

does this look like what you need?