Adding a Formula to an Existing Formula

[magnethero]

Hello all,

I have the following formula:

=OFFSET(INDIRECT(ADDRESS(MATCH($F$2,'Portfolio - Integration (01)'!$D:$D,0),4,,,"Portfolio - Integration (01)"),TRUE),3,-1)

It works perfectly and is part of a summary page of over 100 different tabs (bold to show that it directly references a name so many formulas with 100 different names). I'm trying to create a sum function of ever nth row using the following:

=SUMPRODUCT(D8:D1690*(MOD(ROW(D8:D1690),23)=5))

I know it should work but the problem is that for some of the sum values, in certain years, there was no value and to find and thus a #N/A shows up. This is preventing the SUMPRODUCT from running.

I want to add an encompassing =IFNA( X ,0) to the main formula but to use replace (Ctrl+H) I can only hit one side of the function at a time and it won't allow me to keep any of those value changes so I can add the other side.

How do I add in this =IFNA function without changing one grouping and copying over all my summaries to then have to manually change each tab reference?


Starting point:
=OFFSET(INDIRECT(ADDRESS(MATCH($F$2,'Portfolio - Integration (01)'!$D:$D,0),4,,,"Portfolio - Integration (01)"),TRUE),3,-1)

Goal:
=IFNA(OFFSET(INDIRECT(ADDRESS(MATCH($F$2,'Portfolio - Integration (01)'!$D:$D,0),4,,,"Portfolio - Integration (01)"),TRUE),3,-1),0)

 

[Oscar Cronquist]

Why can't you search and replace the entire formula? (CTRL + H)
There are no relative cell references in your formula or what am I missing?

 

[magnethero]

My problem was that if I used CTRL + H I could do it one block at a time because each of the bolded sections was an individual tab name. I would have had to do over 100 different replace in order to add in the IFNA function. This morning I did figure out a solution to being able to create the brackets on either end.

Dumb the cell by Ctrl + H and remove the = in all cells. Then I just added the two sides in two more replaces [in this case replacing OFFSET with IFNA(OFFSET and then the closing ) with ),0) ] before one final replace for IFNA( to =IFNA( making all the functions live again.