Copying Formula to every second column and still keep in sequence

[Manuel Pedro]

Hi all

I am probably gonna kick myself, but here goes.

I am trying to copy a formula across the columns of my worksheet. However it is a "paste one skip one" issue to be resolved.

The problem I am experiencing is that the formula does copy over but it skips as well.

I.E.: B6 empty D6 empty F6 empty
Column Column Column

Instead of

B6 empty C6 empty D6 empty
Column column Column

Is there a way to get the formula to keep to the sequence required?

Please Help!!

Regards
Manuel dos Santos

[FDibbins]

Hi and welcome to the forum

Nee moenie skop nie!!

What is the formula you atr trying to copy, and what does the "wrong" copy look like?

[Manuel Pedro]

Hi

Thank you for your response.

The Formula I am working with is as follows:

=IF(OR('Prod. & Pack. Attendance'!D7=0),"",IF(AND('Prod. & Pack. Attendance'!D7=0),"",('Prod. & Packaging Control Sheet'!D$35*'Prod. & Pack. Pay Summary'!$B$4)/('Prod. & Pack. Attendance'!D$107))*'Prod. & Pack. Attendance'!D7)

The Part that must change as I copy down the column, only the numbers must change not the letter is the following, ( you will see this Part appears 3 times in the formula):

Prod. & Pack. Attendance'!D7=0

The D7 must change to D8, D9, D10 and so on when I copy down the column. However It must change the letter when I copy across the spread sheet, IE: D7, E7, F7 and so on.

The only part of the formula that must always remain a constant through out the spread sheet is the following:

'Prod. & Pack. Pay Summary'!$B$4

This is where the pay rate is determined.

The following Parts must remain the same when I copy down and change the column letter when I copy across:

'Prod. & Packaging Control Sheet'!D$35
'Prod. & Pack. Attendance'!D$107

Here the Column Letter and Row number must remain the same when I copy down the column, but only the Column Letter must change when I copy across the the Columns of the Spread sheet.

The spread sheet Columns are divided into days, the rows represent the workers. Since I do not know how to copy a part of the spread sheet to give you an example, I will do my best to describe it to you:

Column A & B, Both represent Monday, with Column A being for the Pay for the day & Column B being for any loans taken by the employees. The Loans are entered Manually of Course.

Column C & D will represent Tuesday, in the same way as above, & column E & F will represent Wednesday, & so forth.

The Following 3 Formulas copy correctly when I copy down but incorrectly When I copy Across the columns:

Prod. & Pack. Attendance'!D7=0
'Prod. & Packaging Control Sheet'!D$35
'Prod. & Pack. Attendance'!D$107

The Column Letter "D" has to change to "E" to "F" when I copy Across From Column A to Column C to Column E & so on. Every 2nd column has no formula as the loans are entered manually.

At present the letters change as follows: "D" to "F" to "H" & so on, skipping a column as I go.

Hope this gives you a better understanding of the problem. I am doing this for 200 Employees, across a 12 month period.

I also see that the formula is giving a ERROR of a "CIRCULAR REFERENCE". Can you possibly tell me why?
I have to have a ZERO or a daily wage depending on whether that person has come to work or not for the specific Day in order to get a total at the end of each week.

Regards
Manuel

[FDibbins]

For a start off, I think you can shorten your formula to...
=IF('Prod. & Pack. Attendance'!$D7=0,"",('Prod. & Packaging Control Sheet'!D$35*'Prod. & Pack. Pay Summary'!$B$4)/('Prod. & Pack. Attendance'!D$107)*'Prod. & Pack. Attendance'!D7))

- You have that last part outside the IF() statement, not sure if that was a mistake or not?

Regarding "fixing" certain references, maybe this will help you?
$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors"...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across


If you still have a problem, maybe you could upload a small sample of what you are working with for mr to take a better look?

Geniet jou dag