Autofill Formula in Excel 2000 1

[Gaz1634]

Can any one tell me how to continue a formula on to the next row when two input cell sare completed in Excel 2000.

 

[Dreamboat]

Generally, this is done by using the correct formula.

For instance, if there is nothing in cell E3 and G3, you can put this formula in F3 and it will return nothing, but if there IS something in both of those cells, then the formula will return its value:

=if(or(isblank(E3),isblank(G3),"",G3*E3)

Anne Troy

http://www.TheOfficeExperts.com

 

[Gaz1634]

That does answer my question but I would like to use Excel's built in list extending function.
I should have said that at the beginning!

The problem I have got is that sheet is made up of a list, column A and B contain input, then column C and D contain fomula which work on A and B, this section works correctly, so when cells A and B are filled in cells C and D automatically fill the formula in. This is part of Excel's options for extending format and formula

The problem occurs in column E, which does not continue the formula, I believe this is because I have used Absolute Referencing in the formula which counts a condition in column d, is there any way round this problem?

 

[smah]

It's still unclear exactly what your'e trying to do, but yes absolute referencing can mess up filled formulae - depending on exaclty what you're after. Is absolute referencting required for your situation? Another thing to keep in mind is that you can use partial absolute referencing to just reference a particular colum or row: A$1 or $A1. For more help, include a few rows of what you're trying to do.

 

[Gaz1634]

The spreadsheet is as follows:

[tt]
Lastname Firstname Initials UserID
Smith Paul ps ps1
Smith Peter ps ps2
[/tt]
Initials =LOWER(CHAR(CODE(B1))&CHAR(CODE(A1)))
UserID =C1&COUNTIF(C$1:C1,C1)

The formula for UserID works out how many records in the list are equal to the current and adds the number after the end of the string!

Hope this makes sense, and formats correctly!

 

[Gaz1634]

I forgot to add the problem!

The above spreadsheet works correctly but does not automatically extend the formula on the UserID column, the other columns extend fine, is there any way to get this to do it automatically?

I did think about doing a formula which says if it is not blank then do the formula, but it wuld be better if the sheet could do this automatically.

 

[PeterMoran]

Hi GAZ1634,

Not sure if I understand your problem, but with the formulas you have for cols C and D you will get "#VALUE!" errors if you extend the formulas to rows where you have no entries for cols A and B.

To overcome this you can change your formulas as below:

Initials: =IF(A9="","",LOWER(CHAR(CODE(B9))&CHAR(CODE(A9))))

UserID: =IF(C9="","",C9&COUNTIF(C$1:C9,C9))

Hope this helps!

Peter Moran
Two heads are always better than one!!

 

[Zathras]

I don't think that there is an AUTOMATIC non-VBA way to do what you want. It is possible with VBA and is relatively easy to accomplish. If you want to go there, I suggest you post again, only not here. It would be more appropriate to post in forum707 instead.

Meanwhile, are you aware that there is a MANUAL short-cut way to extend formulas in columns C and D based on data entered in column B? Merely select the last two formulas in the columns C and D, then double-click on the tiny square in the bottom right-hand corner of the selection. The formulas will be copied into all of the rows below, stopping with the last row that has data in column B. This is a great time-saver and may be sufficient for your needs.

 

[Gaz1634]

Thanks for everyone's suggestions, I will have to give VBA a go see if it solves the problem.