[SOLVED]Integrate (Indirect(address))

[lazenca]

I have this formula:

=SUMIF('Fcst template'!$A:$A,$A6&"*",OFFSET('Fcst template'!$A$1,0,MATCH(C$4,'Fcst template'!$5:$5,0)-1,65535,1))

Every now and then, I need to delete/replace the worksheet 'Fcst template' with another one with the same name. When I do this, #REF would appear.

So I stumbled across this (Indirect(address)) function and was wondering if its possible to be integrated into my existing formula.

Thanks

[JBeaucaire]

Create a macro called FixREF and paste this code into the window that pops up.

Please Login or Register  to view this content.

Now run the macro AFTER you put your new Fcst template file in place, it will repair all your broken formulas.

[NBVC]

Try:

Please Login or Register  to view this content.

[lazenca]

JBeaucaire says:

Create a macro called FixREF and paste this code into the window that pops up.
Code:

Cells.Replace What:="#REF", Replacement:="'Fcst template'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Now run the macro AFTER you put your new Fcst template file in place, it will repair all your broken formulas.

Tried that, placed the codes above into a new module, Sub/End Sub and then tried to run it but gives me an error Name argument not found and also it highlights "SearchFormat:=".

NBVC says:

Try:

Code:

=SUMIF(INDIRECT("'"&A1&"'!$A:$A"),$A6&"*",OFFSET(INDIRECT("'"&A1&"'!$A$1"),0,MATCH(C$4,INDIRECT("'"&A1&"'!$5:$5"),0)-1,65535,1))

Tried that too, there's still the #REF problem

[JBeaucaire]

Cut and paste the entire macro here in between CODE tags so we can see if there is a problem.

[lazenca]

Sub FixRef()
Cells.Replace What:="#REF", Replacement:="'Fcst template'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

This is the macro that you gave me. I wonder if I used it correctly.

[EdMac]

Bear in mind that INDIRECT will not work with closed workbooks......

[JBeaucaire]

Well, it works here. (attached)

You can try recording your own version.

1. Create the error situation so #REF is on your sheet
2. Put the new Fcst template sheet in place.
3. Save your test sheet
3. Click on Record a new macro and call it FixRef
4. Now press Ctrl-H to open the Search/Replace window
5. Search for #REF
6. Replace with Fcst template (case sensitive, spell it right)
7. Click on Replace All, your formulas should be repaired now
8. Click the Stop Recording button
9. Go to your macros and Edit the new FixRef macro
10. Copy the entire macro here for us to see.
11. Close the test sheet without saving
12. Reopen the test sheet
13. Go to macros and paste this code you've copied into a new macro manually
14. Run the new macro again and see if it runs cleanly like it did the first time

[NBVC]

Can't you just copy and paste the contents of the sheet over the old one so that you are not actually deleting the sheet in the first place?

[lazenca]

Sorry for the delayed reply. Was not at work.

JBeaucaire says:

Well, it works here. (attached)

You can try recording your own version.

1. Create the error situation so #REF is on your sheet
2. Put the new Fcst template sheet in place.
3. Save your test sheet
3. Click on Record a new macro and call it FixRef
4. Now press Ctrl-H to open the Search/Replace window
5. Search for #REF
6. Replace with Fcst template (case sensitive, spell it right)
7. Click on Replace All, your formulas should be repaired now
8. Click the Stop Recording button
9. Go to your macros and Edit the new FixRef macro
10. Copy the entire macro here for us to see.
11. Close the test sheet without saving
12. Reopen the test sheet
13. Go to macros and paste this code you've copied into a new macro manually
14. Run the new macro again and see if it runs cleanly like it did the first time

Hmm I see that there's 2 worksheets in the attachment. The first worksheet contains #REF. Does this mean that its not working already?

NBVC says:

Can't you just copy and paste the contents of the sheet over the old one so that you are not actually deleting the sheet in the first place?

Thought about this before, but just can't do it because if I am overwriting everything, it still shows #REF.

PS: Really sorry if I can't show you the real file due to company's policies. But I will try to make myself as clear as possible.

Thanks still

[JBeaucaire]

It's a macro. I left the fault situation in place so you can see it, then run the macro yourself. Run the macro.

[lazenca]

Amazing that I didn't tried to use recording. Ding, it works!

Please Login or Register  to view this content.

Thanks alot man!
[SOLVED]