desperately need help! need to copy formula without a range changing, or write VBA for index array

[doodlebug323]

I have written arrays to populate the following information:

1) the number in the 1st cell in the range containing a NUMBER to F5:
{=INDEX(N5:AR5,MATCH(TRUE,ISNUMBER(N5:AR5),0))}
2) the number in the cell at the top of the page that corresponds with the cell found by this array to E5:
{=INDEX(N4:AR4,MATCH(TRUE,ISNUMBER(N5:AR5),0))}

3) the number in the last cell in the range containing a NUMBER to H5:
{=INDEX(N5:AS5,MATCH(9.99999999999999E+307,N5:AS5))}
4) the number in the cell at the top of the page that corresponds with the cell found by this array to G5:
{=INDEX(N4:AR4,MATCH(9.99999999999999E+307,N5:AR5))}

However, I have 12 sheets per excel workbook and each sheet may end up containing 500+ rows... way to many to copy and paste this to each cell and then correct the formula as needed (so far each time I copy and paste to a new row it changes N4:AR4 to the row above where I'm pasting, I can't get it to remain the same... if it would remain the same I'd be happy to copy and paste... so, on that note, is there a way to make that one piece remain the same, or is there a way to write this to VBA to apply it to the whole workbook?
Please let me know if you have questions, I'm in desperate need of assistance, any assistance will be greatly appreciated.
Thank you!!!!!!

 

[Taul]

Two things that may help

1) Add a ‘ sign in front of the formula, this will convert it to text so you can copy and paste without the range changing. Remove the ‘ sign from the new and original locations, to make them active formula again.

2) Provided your sheets are laid out in the same way (cell for cell) Select one page tab, hold down the ctrl key and click on the other page tabs that you want to affect. Add the data or formula to the say cell D5 and the data will also be copied to cell D5 on all the selected page tabs.

 

[Taul]

Also, if you are copying the formula down the page, you may wish to fix one or more of the formula terms so they do not change. Use the $ sign to do this.

For example, formula =SUM(A2*B2) copied down will change to =SUM(A3*B3) on the next row.

If you add a $ sign in front of the 2 like this =SUM(A$2*B2) it will fix the calculation to multiply by the value in row 2 for all of the formula that you copy down.

A quick way to add or remove the $ signs, is to select the formula (or part of the formula) in the edit line at the top of the page and press F4 to toggle the $ sign being added to:-
Letter and number; Letter only; Number only; Off

 

[doodlebug323]

Thank you very much! It worked perfectly. Have a great day!