copying from one sheet to another

[gss]

My main sheet has a couple drop down menus controlled by vba which depending on what is selected, it assigns cells differently. For example, from the drop down menu, if 'A' is selected, cell G16 will be assigned as "abc", but if 'B' is selected from the drop down menu, cell G16 will be assigned as "xyz".

Just for additional information as it may help, "abc" and "xyz" are actually assigned around G90 and G91, but when 'A' or 'B' are selected, it cuts "abc" and "xyz" for G90 or G91 and pastes them to G16.

What I am trying to do is have cells in another worksheet show what is in the first screen. But if I assign cell G16 in Sheet2 as 'Sheet1'!G16, it will work if 'A' is selected from the drop down menu, but return #REF! when 'B' is selected from the drop down menu.

Does anyone know how I can do this using vba (not preferred) or otherwise.

Thanks.

[JBeaucaire]

How about something in your cell that does the job with a simple IF() test:

=IF(A2="a",$G$90,IF(A2="b",$G$91,""))

[gss]

How about something in your cell that does the job with a simple IF() test:

=IF(A2="a",$G$90,IF(A2="b",$G$91,""))

That's the way I was planning on going if I couldn't figure out an easier way. The only problem is I have a LOT of different options from several different drop down menus, and I have values in a LOT of cells. This way will take a really long time.

[JBeaucaire]

Then you need to step back from your sheet and do some reorganization. Scattering reference values all over the place is non-intuitive and confusing to anyone other than the person who built the sheet.

If you have a LOT of "comparison" values, then you should chart them and reference the chart. Something like this on your sheet off to the right somewhere:

Please Login or Register  to view this content.

Then, instead of the "IF" approach shown earlier, you use a simple INDEX/MATCH formula against the chart.

=INDEX($K$1:$K$10,MATCH(A2,$J$1:$J$10,0))

That chart can scale up to any size you want. As long as the appropriate "response" data is in the matching row, you can use this.

[gss]

I agree with you. This spreadsheet was made by someone who used to work for the company. He's gone so I was left in charge of modifying it and it took me forever to get a hang of everything that's going on.

Anyways, the chart thing worked.

Thanks for your help!