Conditional Lookups or Dynamic Cell References?

Worried Guy

New Member
Joined
May 11, 2002
Messages
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/df94e31c/bc/My+Documents/AnnTgtsnotes.xls?bc3J_38AIdk0yVti
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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.

:confused:

Lee
This message was edited by Worried Guy on 2002-05-13 13:55
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top