Conditional Lookups or Dynamic Cell References?

Worried Guy · May 13th, 2002, 09:54 AM

This is a follow on to my earlier mistaken conditional formatting query.

Is it possible, using only Excel functions, for one cell to absolutely reference a second cell value in another worksheet based on the value of a third cell?

I cannot use a nested 'IF' statement as there are 12 different conditions. Can a lookup function fit the bill here to first check the value of the third cell and then fetch the value of specific cell in another 'hidden' sheet.?

Furthermore, if the data is then changed by the user in the first cell, those changes are updated in the second cell.

I have uploaded the source file here.

http://us.f1.yahoofs.com/users/df94e...3J_38AIdk0yVti

Mark W. · May 13th, 2002, 10:01 AM

=INDIRECT("Sheet2!"&Sheet1!A2)

PDuPre' · May 13th, 2002, 01:34 PM

If you want to check the cell and then do one of 12 different actions I have used the CHOOSE formula. Much nicer than nested Ifs and not limited to 7.

I have not used INDIRECT. But, it looks interesting.

Philip

Worried Guy · May 13th, 2002, 02:43 PM

Thanks for that PDuPre'

The CHOOSE function works fine for "getting" the correct values from the second cell(s) but if I want to update the values in the second cell using the first cell, it overwrites the CHOOSE formula.

I've tried using an IF [Conditional Cell]=RequiredValue then 2nd Cell Value=1st Cell Value. However, this creates a circular reference and again overwrites the CHOOSE formula in the first cell.

I don't really know Excel, I'm learning all the time, but I think there's an 'easy' method to sort this out that's eluding me.

Lee

[ This Message was edited by: Worried Guy on 2002-05-13 13:55 ]

Worried Guy · May 13th, 2002, 04:48 PM

I think this is going to require the timely intervention of a VBA Don to solve this chestnut. Every formula I use will be overwritten the first time data is entered into a cell and if I want to go back to amend any values, it will not work anymore.

PDuPre' · May 14th, 2002, 05:57 AM

Not sure why the original formula is being overwritten. Is someone actually typeing a number in the cell over the formula? Or are you using an excel add-in to retrieve data from a database?

If the former then you can have the data entry in a third cell. If the latter then there is probably an option to retain formulas. This is Excel, so the add-in would expect people to have complex formulas.

Still, if you have a solution, but the formula keeps getting over-written, you could simply record a macro of your actions to enter the required formulas etc. after each retrieve.

I hope this helps.

Philip

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 09:54 AM	#1
Worried Guy New Member Join Date: May 2002 Location: England Posts: 6	This is a follow on to my earlier mistaken conditional formatting query. Is it possible, using only Excel functions, for one cell to absolutely reference a second cell value in another worksheet based on the value of a third cell? I cannot use a nested 'IF' statement as there are 12 different conditions. Can a lookup function fit the bill here to first check the value of the third cell and then fetch the value of specific cell in another 'hidden' sheet.? Furthermore, if the data is then changed by the user in the first cell, those changes are updated in the second cell. I have uploaded the source file here. http://us.f1.yahoofs.com/users/df94e...3J_38AIdk0yVti

May 13th, 2002, 10:01 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=INDIRECT("Sheet2!"&Sheet1!A2)

May 13th, 2002, 01:34 PM	#3
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	If you want to check the cell and then do one of 12 different actions I have used the CHOOSE formula. Much nicer than nested Ifs and not limited to 7. I have not used INDIRECT. But, it looks interesting. Philip

May 13th, 2002, 02:43 PM	#4
Worried Guy New Member Join Date: May 2002 Location: England Posts: 6	Thanks for that PDuPre' The CHOOSE function works fine for "getting" the correct values from the second cell(s) but if I want to update the values in the second cell using the first cell, it overwrites the CHOOSE formula. I've tried using an IF [Conditional Cell]=RequiredValue then 2nd Cell Value=1st Cell Value. However, this creates a circular reference and again overwrites the CHOOSE formula in the first cell. I don't really know Excel, I'm learning all the time, but I think there's an 'easy' method to sort this out that's eluding me. Lee [ This Message was edited by: Worried Guy on 2002-05-13 13:55 ]

May 13th, 2002, 04:48 PM	#5
Worried Guy New Member Join Date: May 2002 Location: England Posts: 6	I think this is going to require the timely intervention of a VBA Don to solve this chestnut. Every formula I use will be overwritten the first time data is entered into a cell and if I want to go back to amend any values, it will not work anymore.

May 14th, 2002, 05:57 AM	#6
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	Not sure why the original formula is being overwritten. Is someone actually typeing a number in the cell over the formula? Or are you using an excel add-in to retrieve data from a database? If the former then you can have the data entry in a third cell. If the latter then there is probably an option to retain formulas. This is Excel, so the add-in would expect people to have complex formulas. Still, if you have a solution, but the formula keeps getting over-written, you could simply record a macro of your actions to enter the required formulas etc. after each retrieve. I hope this helps. Philip