Having cells change their contents according to what the contents of other cells are

[I Am Herenow]

I have a column in my .XLS whose contents I want to change depending on what the contents of the cells of a different column are. However, I only want two cells from the same row to be dependent on each other. So, for example, if $A$1 reads "1" then $B$1 should read "apple" and if $A$2 reads "1" then $B$2 should read "apple"; if $A$12 reads "3" then $B$12 should read "pavement" etc.

Any help would be appreciated!

[royUK]

I think you need to look at one of these Functions - VLOOKUP or CHOOSE.

See

http://www.excel-it.com/excel_functions.htm

[nsenor udofa]

I hope you are familiar with VBA. Copy and paste the following code in the code module for the sheet you are working with (right click the sheet tab and select 'View Code'). Change the X_ and Y_ Values correspondingly as desired. You may also change the column index for the X_ and Y_Values by changing the const declaration values of lX_Col and lY_Col

Please Login or Register  to view this content.

[I Am Herenow]

Thanks for the help

[I Am Herenow]

Edit: NVM, see this.

[nsenor udofa]

This is the code for your worksheet that I think you need.

The validation on activation will help you or your user select the proper text.
In addition to the X_ and Y_ value functions, the Link_Values function is also introduced to hold the hyperlink addresses. The Y_Links are derived from the title of the link web pages.

I hope you understand the logic. ENJOY!

Please Login or Register  to view this content.

[I Am Herenow]

Thanks for the help, but at the moment "Private Function Link_Values()" all shows up red and it gives me a syntax error when I try to use the code.

[nsenor udofa]

Add a double quote to the begining of line 5 of the procedure. Or copy and paste the code again. I have corrected the error.

[royUK]

I can't see why you need VBA to do this. A simple VLOOKUP or CHOOSE would work.

[nsenor udofa]

Except for very simple situation, i prefer code behind objects to formulas. I believe it hides implementation from the user and make the objects easier to use. Is there a disadvantage to this for Excel?

[royUK]

Except for very simple situation, i prefer code behind objects to formulas. I believe it hides implementation from the user and make the objects easier to use. Is there a disadvantage to this for Excel?

VBA will only work if macros are enabled
Excel's Functions are generally faster than UDFs.
Why re-invent the wheel?

[nsenor udofa]

VBA will only work if macros are enabled
Excel's Functions are generally faster than UDFs.
Why re-invent the wheel?

Of course Excel functions are faster than UDFs!

But I am talking about code behind object - Responding to user input to change parameters and cell values.

In my experience, if you have a lot of calculations to perform (like 1000 long formulas in col 2 cells dependent on adjacent col 1 cells etc) and you are comfortable with VBA, using a worksheet_change event routine for your calculation and targeting col 1 is about 1000 times faster than Excel functions that all 1000 are likely to recalculate at every change in cell value if you don't want to loose interim accuracy.

Think about it.

ps. enabling macro is not such a big deal, is it?