Indirect or Absolute Formula auto-fill to update reference, possible?

Originally Posted by brandoncartwrig

I want to be able to paste it and have the references change automatically. Example
=INDIRECT("'Monthly Deductions-Elections '!$M$11")*12/26
=INDIRECT("'Monthly Deductions-Elections '!$M$12")*12/26
=INDIRECT("'Monthly Deductions-Elections '!$M$13")*12/26

Delete the $ sign in front of the row number, like this:
=INDIRECT("'Monthly Deductions-Elections '!$M12")*12/26

$ makes the reference absolute, so deleting it makes the reference relative so it can change.

Originally Posted by brandoncartwrig

Hello,

I'm using Excel 2003 for payroll deductions. I have one workbook with two worksheets. One worksheet (information sheet) is were I enter all of the information. The other (payroll sheet) calculates the information from the first. I want to use an indirect formula so if a co-worker goes into the information sheet and cuts and pastes something, it won't change the formula on the payroll deduction sheet. Is there a way to auto-fill down the indirect formula so I won't have to type it in thousands of times?

To clarify, this is the formula
=INDIRECT("'Monthly Deductions-Elections '!$M$11")*12/26

If I copy and paste, or auto-fill with the mouse, to a different cell the exact same formula is pasted there.

I want to be able to paste it and have the references change automatically. Example
=INDIRECT("'Monthly Deductions-Elections '!$M$11")*12/26
=INDIRECT("'Monthly Deductions-Elections '!$M$12")*12/26
=INDIRECT("'Monthly Deductions-Elections '!$M$13")*12/26

Hi,

the formula shown

=INDIRECT("'Monthly Deductions-Elections '!$M$11")*12/26

appears to have no use for the Indirect function.

You can utilise the function as something like

=INDIRECT("'Monthly Deductions-Elections '!$M$"&Row()+10)*12/26

where the row number for the formula is an offset of the current row()

Let me know how you go.
---

Originally Posted by brandoncartwrig

Thanks for replying.

I need to keep the references absolute so that if a row is added above or below the reference it won't change the formula.

Also, I've noticed if I try to auto-fill down the INDIRECT formula without the absolute reference it won't change the reference, which is what I'm after in this case.

---


The reason I need to use the INDIRECT funtion for this formula is so it won't change if someone Cut and Pastes or drag and pastes the reference cell.

>> I was not able to get the formula you gave me to work right.

I did find a solution to my problem, but a new problem has surfaced. I'll tell you how I fixed it, and what that new problem is tomorrow. My office is closing and I've got to head home now.

To define 'absolute', if you change the colour of A1 to red, and then insert a row brfore 1, the red cell is now A2.

Did you want to refer to the red cell, or did you want to refer to the cell that is the top left cell of the spreadsheet (A1)?

Perhaps you want to add the balance from the previous row, which fails when you add or delete intervening rows in a column, for that, in B2, you can use

=OFFSET(A2,-1,0)

ie, in B2 =A2+OFFSET(B2,-1,0)

hth
---

Originally Posted by brandoncartwrig

Okay here is the solution I came up with to what I was trying to do.

First of all, the formula on the first page has to be an INDIRECT formula with an abosolute reference ($A$1) to the second page. The reason it has to be an abosulte reference is, incase a row is added or deleted above or below the reference. If a row is added or deleted, it will change the reference in the formula if it is not absolute. The reason the formula has to also be an INDIRECT formula is becasue even though the reference is absolute it will change if the reference cell is cut and pasted or drag and pasted to a new location. The problem is when I create an INDIRECT formula with an absolute reference, I can't auto-fill the formula to multiple cells and have the absolute reference update to the corosponding reference.

So here is what I did.

I created another worksheet like this. Please refer to the PDF attachment INDIRECT FORMULA.

I typed the different parts of the formula into seperate cells.

Typing the absolute reference as text in D1 allowed me to auto-fill down giving me $M$1, $M$2, $M$3, and so on.

I typed in a combining formula, which you can see in cell F1, to combine all of the cells and create the complete formula I need for my other workbook as a result. Then I selected cells A1, B1, C1, D1, E1, and F1 and auto-filled down 1000 rows. Finaly, I selected and copied column F and then paste specialed the values only in column G.

That left me with one problem. Even though column G contains the new formula, Excel reads it as text unless I double-click on each individual G cell and press enter. Then Excel reads it as the formula it's supposed to. I've got thousands of these formulas. Does anyone know of a way to make Excel read all them as a formula with just a few clicks of a button? Its not just the show/hide formula function. I've tried that.

Thanks

I guess my difficiency in the area of comprehension is starting to show, but I have little (if any) idea of what you said, nor why you did what you did.

You cannot make-up an instruction the way you appear to have made it and expect that it will operate the way you were hoping.

Your .pdf is nice, but of little use in a forum, a sample .xls would have given a chance to see what you wee trying to do, however very few helpers want to expend time transposing details from your typing or displays to make an accurate test book.

I have no idea why you would add a space to the end of a Worksheet name as you appear to be indicating in the C of your .pdf.

You refer to "an abosolute reference ($A$1) to the second page" but you then display an A1 which contains just an = sign

You say "Please refer to the PDF attachment INDIRECT FORMULA.". I presume that you meant the .pdf of a similar name and that you had not posted the wrong .pdf

The closest guess to an answer for what you are trying to do is still,

in A1 put

Monthly Deductions-Elections

with no leading nor trailing spaces, and name the sheet accordingly.

in B1 put

=INDIRECT("'"&A$1&"'!M"&ROW())

and formula fill that downwards.

Does this do what you wanted?
---

Originally Posted by brandoncartwrig

I'm using Excel 2003 for payroll deductions. I have one workbook with two worksheets. One worksheet (information sheet) is were I enter all of the information. The other (payroll sheet) calculates the information from the first. I want to use an indirect formula so if a co-worker goes into the information sheet and cuts and pastes something, it won't change the formula on the payroll deduction sheet.

Out of sheer desperation: I have the same problem as you, Mr. Cartwrig. If you found a feasible working solution, could you please post it here in this thread so that other users (incl. myself) can benefit from it? Many thanks in advance.