drag and fill formula problem

bklefos

New Member
Joined
Jul 19, 2007
Messages
6
I am working on a summary sheet which in each cell a formula exists. This formula compares a cell on sheet 1 to sheet 2. If sheet 1 and sheet 2 do not exist it will read ERROR. This is what i want. However, when i try to drag my formula I cannot get excel to understand the pattern i would like it to drag.

Here is my formula. Each sheet is labeled as Results ()

=IF('Results (1)'!H11='Results (5)'!H7,"","ERROR")
I also typed in the cell to the right correctly as
=IF('Results (2)'!H11='Results (5)'!H8,"","ERROR")
Those are in columns A & B

When i drag the formulas off to the right i get this in columns C & D
=IF('Results (1)'!J11='Results (5)'!J7,"","ERROR")
=IF('Results (2)'!J11='Results (5)'!J8,"","ERROR")

The proper calculations should be
=IF('Results (3)'!H11='Results (5)'!H9,"","ERROR") IN CELL C
=IF('Results (4)'!H11='Results (5)'!H10,"","ERROR") IN CELL D

How do i keep some of my calculation constant and some as if it is counting up? When i drag to fill it automatically repeats the calculations as if i want it 1,2,3,1,2,3 rather than 1,2,3,4,5,6

Any help would be great and would save me lots of time
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Change your formula in cell A11 from:
=IF('Results (1)'!H11='Results (5)'!H7,"","ERROR")
to
=IF('Results (1)'!$H$11='Results (5)'!$H7,"","ERROR")

The prefix, $, makes the following column (or row) absolute, that is, unchanging.

Now, just copy your formula in A11 to the left, as far as you need.
 
Upvote 0

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,003
RALPH,

I think the op wants the sheet numbers to change automatically, ie Results(1) to results(2) and so on,

BKLEFOS

can you tell us which cells the formuls will be in, ie RESULTS(5) cell A10

then we can alter your formula to cope with the changes you require
 
Upvote 0

bklefos

New Member
Joined
Jul 19, 2007
Messages
6
Ralph-

The summary report is 69 square cells. Both columns and rows are listed 1 to 68 as a title. Each number represents a team. This is a sheet i am putting together for a golf outing. Every team gets to bet against another team. Every team has there own sheet with 68 cells listing every team. There bet against every team will be entered in the computer. The goal on this summary sheet to make sure that the bet that team #1 wrote on their sheet is equivalant to the bet written down as team #5 has written down as a bet with team #1. Meaning if sheet 1's bet does not equal sheet 5's bet than there is an error. However with 68 team sheets i am having difficulty dragging the sheets and having the sheet numbers follow my pattern.


=IF('Results (1)'!$H$11='Results (5)'!$H7,"","ERROR") IN CELL B6
=IF('Results (2)'!$H$11='Results (5)'!$H8,"","ERROR") IN CELL C6

columns A and row 1 is used for titles

I do know i want to add the $ in parts of it. But how do i continue the count of results sheets on the left to add up and the results on the right to stay the same? Its like on both sides of the equals sign there is two parts to the equation. The results on the left and the H_ always need to change by one and the others need to stay constant.

I hope this is the right information you need to help me.

Thank you.

Brian
 
Upvote 0

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Shippey, if the OP wants Results(1) and Results(2) to occur in pairs, as in columns A and B, then in C and D, and so on, he can copy the formula in column A to column B, then change the (1) for a (2) in column B. Then, he can copy the formulas from A and B to C and D, and so on.
 
Upvote 0

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Brian:

Maybe someone else will help you, before I can. Me, I'm slow and need to understand the problem very well. Then, I can usually solve it. But, as I said, I'm slow. However...

In order to try to help you further, Brian, I will need you to post the formula you have in the first four cells of row 6, for which you have already posted two, cells B6 and C6, so, I need those for D6 and E6. I will allso need the formulas for the four cells, B7:E7.

Ralph
 
Upvote 0

bklefos

New Member
Joined
Jul 19, 2007
Messages
6
=IF('Results (1)'!$H$11='Results (5)'!$H7,"","ERROR") IN CELL B6
=IF('Results (2)'!$H$11='Results (5)'!$H8,"","ERROR") IN CELL C6
=IF('Results (3)'!$H$11='Results (5)'!$H9,"","ERROR") IN CELL D6
=IF('Results (4)'!$H$11='Results (5)'!$H10,"","ERROR") IN CELL E6

=IF('Results (1)'!$H$12='Results (6)'!$H7,"","ERROR")IN CELL B7
=IF('Results (2)'!$H$12='Results (6)'!$H8,"","ERROR") IN CELL C7
=IF('Results (3)'!$H$12='Results (6)'!$H9,"","ERROR") IN CELL D7
=IF('Results (4)'!$H$12='Results (6)'!$H10,"","ERROR") IN CELL E7
 
Upvote 0

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Brian:

O.K., I understand what you need. Here is my formula solution:

In B6 of the Master sheet, enter:
=IF(INDIRECT("'Results ("&COLUMN()-1&"'!H"&ROW()+5)=INDIRECT("'Results ("&ROW()-1&"'!H"&COLUMN()+5),"","ERROR")

Copy the above formula over the whole range. Done.

Note: Since the cell references are all obtained within the INDIRECT() function, the absolute symbol, $, is no longer necessary, and I have deleted it in the formula.

Let us know if this solves your problem/
 
Upvote 0

bklefos

New Member
Joined
Jul 19, 2007
Messages
6
I copied that formula into cell B6. I then went to result sheet 1 and 5 and imput bets to test and see if it worked. Cell B6 gives me a "REF" "invalid cell reference error"
 
Upvote 0

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Brian:

Bear with me, I found a small error that has to be cleared up. I forgot to close my parenthesis for the sheets, as in RESULTS (1. So, here is a corrected formula, which, I believe, WILL work. Check it out.
In B6, enter:
=IF(INDIRECT("'Results ("&COLUMN()-1&")'!H"&ROW()+5)=INDIRECT("'Results ("&ROW()-1&")'!H"&COLUMN()+5),"","ERROR")

When placed in cell B6, the formula should interpret things like this:
=IF('Results (1)'!H11 = 'RESULTS (5)'!H7,"","ERROR")
 
Upvote 0

Forum statistics

Threads
1,190,764
Messages
5,982,797
Members
439,798
Latest member
tangojuliet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top