Replies

That’s what I try to do.

But it clears out immediately, because somehow that key combination is already living somewhere in my machine. It’s like when the previous icons were deleted from the Office 2003 uninstall, the key combination remained. Where is it? And how do I delete/reset it?

That’s exactly what I did – somehow. Thank you!

ok, that’s odd – even when I directly change the value of a cell, the color doesn’t change until I click back on it…

that’s it – lovely! and – I know I could find this if I look around, but if you know off the top of your head:
– is there a way to get it to update automatically, or will the end user always need to click in “A1” to get the color to update?
– is there a listing somewhere of VBA colors?

Many, many thanks!
Angela

Would this do what you want?

=IF(“UTI Protocol”=B1,1,0)

YES – but how do I get the “B1” in there without manually updating each formula? I love Excel’s Replace function – guess I’m just wanting more from it than it can deliver…

Can you provide a more detailed description?

This should be close enough for what I’m trying to do:

Let’s say you wanted to replace the second “UTI Protocol” in the first column’s formula with whatever value was in the second column, rather than with an explicit reference. Is there a way to do this?

(note that I can’t “drag down” the formula because other pieces of the formula are individually set)

THANK YOU for being willing to spend time on this little monster…

I don’t understand – each formula has an external link *and* you want it to refer to the “cell next to it”?
Can you try to explain more clearly?

yes: the formulas refer to various worksheets, but I want to do an index lookup of a value that I enter in the column next to my cell (rather than something static).

I can do the replace cell by cell, but there are over 250 rows of formulas – time consuming AND error prone!

Does that make it clearer?

oh-my-word that’s so much simpler!

Once again, Hans, you’ve saved the day!

Angela

I’m using the Double Lookups formula from here: http://www.cpearson.com/excel/TablesAndLookups.aspx

Do you think it’s the OFFSET function maybe??

OK, that’s a start; my problem is that I have about 30 different closed workbooks that all have data that I want to bring into this workbook. Would I have to create 30 subroutines then? At this point I’m thinking I may just create a tab in each of the closed workbooks that runs the array formula, and then have this one just capture the linked data.

follow-on question: must named ranges always be referred to globally throughout the workbook? Or can there be the same range named on each tab, referring to the data in that tab?

Hans, you are beautiful! I couldn’t figure out what to search on and wasted 45 minutes trying to figure it out -thank you!

Is there a way to do this outside of VB?

I would just like a cell to display a specific value from a range.

I never cease to be amazed at all I learn on this site…

You mean I can’t have my cake and eat it too?? Such a puzzling world…

Thank you – this is very cool and I would never have thought of it!