Copying Indirect formulas

  • Thread starter Thread starter wmjenner
  • Start date Start date
W

wmjenner

I have the following formula:

if($k1<>0,vlookup(indirect("f1"),Sheet2!$A$5:$c$600,2,false," ").

The lookup value (f1) changes constantly via copy/paste which result
in an #REF error whenever the data in column K is updated. That's th
reason for using the Indirect formula.

Now I've discovered the same problem with $k1. When I copy/paste t
update the values in column K (the lookup source), then this formul
returns an #REF error. So now I want to replace If($k1<>0) wit
If(indirect("k1")<>0 so that it will evaluate whatever is in field k
and not try to second-guess how it got there.

The problem, however, is that there are about 1,000 lines in column
with this formula. Is there a way to either edit/replace or write
simple macro? I tried to do it in pieces but Excel doesn't seem t
like replacing things with elements that create invalid formulas.

Any ideas? Thanks!

Bil
 
Hi
you may try
if(INDIRECT("k" & ROW())
0,vlookup(indirect("f1"),Sheet2!$A$5:$c$600,2,false," ")
Click to expand...
and copy this formula down if your formula starts in row 1
 
1. Your Edit/Replace method seems good to me.

2. The INDIRECT() function uses a string argument, so you need to hav
string values for row and column numbers that you can concatenate. Fo
example, I use a hidden row which contains the relative lookup colum
letters to the current column, and a hidden column which contains th
relative row numbers. Then a formula can be copied that refers to thes
cells like :-

=INDIRECT(C$1 & $A2)


There are numerous possible variations
 
Edit/Replace doesn't work because you can't put in the quotes require
for the indirect command. So if I want to change
if($k1<>0....)
to
if(indirect("k1"<>0....)
there is no way to use edit/replace - at least none that I can come u
with syntax-wise. Anyone?

I'll have to play with your concatenate idea - that sounds like i
might be an option
 
First did you try Frank's suggestion. If that worked, then stop reading...



I put
=if($k1<>0,"A","B")
in a few cells

I selected those cells and did:

Edit|replace
Find what: $k1
replace with: indirect("k1")

And was left with a bunch of formulas like:
=IF(INDIRECT("k1")<>0,"a","b")

It seemed to work ok for me.

I just reread your original message.
I changed your original formula (some typos in the post???):

=if($k1<>0,vlookup(indirect("f1"),Sheet2!$A$5:$c$600,2,false," ")
to
=IF($K1<>0,VLOOKUP(INDIRECT("f1"),Sheet2!$A$5:$C$600,2,FALSE),"")
and dragged down a bunch of rows.

Then I did a series of changes.

First, I wanted the formula to be text--so I could make a replacement and not
have excel yell about it being an invalid formula.

1. Replace =
with $$$$$ (some unique set of characters)
2. replace $k
with Indirect($k
3. replace <>
with ")<>
4. replace $$$$$
with = (to convert them back to formulas)

And I got this:
=IF(INDIRECT("k1")<>0,VLOOKUP(INDIRECT("f1"),Sheet2!$A$5:$C$600,2,FALSE),"")

seemed to work ok.

I'm not sure how F1 fits into your plan, but if that doesn't change, aren't you
just retrieving the same value for each formula (if it's <>0)?

And if you meant to change F1 to F1...F999, you could use a similar approact.
Change to text and pick apart what you need to change. Then convert it back to
a formula.
 
Back
Top