Replace a formula with it's value - do I need to macro?

[mirrorsaw]

Thank god this forum exists, ok ... here goes ...

I have a spreadsheet with 2 columns (let's pretend), the first column uses Data Validation for a dropdown list. The second column contains a formula that references the 1st column and uses lookup:

=IF(A35<>0;LOOKUP(A35;NewCons;NewGroups))

So as soon as a value is selected in column A, column B changes to display a value.

Rather than just displaying this value, I would like the cell in column B to be completely replaced by this value, so goodbye to the formula.

Is there a way that I can do this? Will I need to go and study how to use macros?

Thanks in advance

[Winterbay]

Before I try to answer let me just ask you: Why do you not want the formula?

[Cheeky Charlie]

(let's pretend)

I'm a cowboy



Basically, yes, it's much easier to do this with a macro than without, but it looks like a pretty easy operation... try this:

Right click on the sheet tab - click view code - paste inside then close that window

Please Login or Register  to view this content.

Now type in A35...

HTH

[mirrorsaw]

Before I try to answer let me just ask you: Why do you not want the formula?

Because as time goes on the data that is being referred to in the LOOKUP will change, but we don't want these cells to automatically update when that happens -- we want to keep these cells "historic"...

[mirrorsaw]

wow this is impressive, though I don't entirely understand it ---

now would it be simple to change it so that it effects all cells in column A/B, and not just row 35?

I'm a cowboy



Basically, yes, it's much easier to do this with a macro than without, but it looks like a pretty easy operation... try this:

Right click on the sheet tab - click view code - paste inside then close that window

Please Login or Register  to view this content.

Now type in A35...

HTH

[Cheeky Charlie]

Thanks,

Change this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

HTH

[Winterbay]

Ah right, good reason. Also yes a macro is probably the easiest solution, but you already have one now so

[mirrorsaw]

For some reason this doesn't seem to be working.
I've tried to work out what the code is doing,
is it just checking the cell (when it's targeted), and if it's in column 1 (column A?) AND if target(1,2) (which I assume means the cell to the right) is empty, it runs the lookup function
but
What does this Range part do?
Couldn't it be simplified so if any cell in this column is targeted, it runs the lookup function and sets the value of target(1,2) to the result of the lookup?

thanks in advance for excusing my ignorance

[Cheeky Charlie]

Hehe! Sorry, that was idle of me, I didn't check my reply thoroughly. Try this:

Please Login or Register  to view this content.

(Yes, you understand the code, and so you should be able to understand what I forgot to do!)

HTH

[mirrorsaw]

Sorry guys I'm still struggling with this

As a test, I created a new workbook.
I copied this code you've supplied and pasted it into sheet1's code.
Then on sheet2, in columns A and B I put values a,b,c,d and e ... and 1, 2, 3, 4 and 5.
Selected each set and gave them names, newcons and newgroups.
I assume this is all that's required?

Then I went to sheet 1 and entered a value in cell A1
As soon as I clicked away I got a macro error.

Please help

[Cheeky Charlie]

The code didn't allow for the lookup ranges being on a different sheet, you will need to change the sheet name in the code below

Please Login or Register  to view this content.

HTH

[mirrorsaw]

AHHH finally got it working the way I wanted

Thankyou thankyou thankyou so much!!

[arlu1201]

Jeferson11,

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.