Conditional cell reference inside formula?
Posted by George Eeds on January 26, 2002 12:06 PM
Can a formula's cell reference change automatically if another cell's formula is changed? Example: In sheet2, cell A1 is =sheet1!$A$17 and cell A2 is =sheet1!$B$17. If I change cell A1 to =sheet1!$A$18, I want cell A2 to update to =sheet1!$A$18. In other words, I want cell A2's row reference to be determined by cell A1's row reference.
Posted by Jack in the UK on January 26, 2002 12:19 PM
Hi George--
My take on your question is this if i cange a formula in a cell will the formula in another cell change, the sadly no.
The reason is function return results ONLY and so can not action (change things or do a command) to do what you need will be very complex VBA and coded very well, stick arround some guru programmers might like the challenge and everyone will save that page and adapt it
HTH
Jack
If ive got it wrong im sorry im misunserstood your questions
Posted by Robb on January 26, 2002 7:22 PM
George
I presume you meant that, if you change A1 to =Sheet1!$A$18, cell A2 should update to =Sheet1!$B$18 - in other words, the relative position of the cells will
remain constant. If this is so, you could try using a range name for A1. This would result in all references changing whenever you changed the definition of the
range name. To illustrate what I mean:
- Using your example, give range Sheet1!A17 a name, say Rg
- In Sheet2!A1, enter =Rg
- In Sheet2!A2, enter =Offset(Rg,0,1)
Values in Sheet2 A1 and A2 should now reflect the values in Sheet1 A17 and B17 respectively
If you want to update all the formulae with new references, simply amend the definition of the range named Rg
to, say Sheet1!A18. Values in Sheet2 A1 and A2 will now reflect the values in Sheet1 A18 and B18
respectively.
You could extend this method to any number of cells you want to make relative to another.
Any help?
Regards
Robb
Posted by George Eeds on January 26, 2002 8:57 PM
Thanks Robb-this shows promise
Posted by Paul on January 27, 2002 5:52 AM
One work around is to create your row reference in lets say a3. Put 17 in cell a3.
A1 is =indirect("sheet1!$a$"&a3)
A2 is =indirect("sheet1!$b$"&a3)
Change the value of a3 whenever you want the row reference to change.
Let me know if that helps.
Posted by George Eeds on January 29, 2002 10:01 PM
conditional cell reference inside formula
Thanks, Paul. This is a great solution for my spreadsheet. Robb's works too. Yours is a perfect fit for my application.
One work around is to create your row reference in lets say a3. Put 17 in cell a3.
