using relative reference in macros

[ilundy77]

hi all,

a user i'm assisting wants to calculate totals by recording a macro that sums all the rows in a column using relative cell references. this is because the user runs a report that pulls in data that varies. one week the report may contain 40 rows that need to be totaled, and the next week there may be 60 rows that need to be totaled.

here's what i did:

• place cursor in cell where i want total to appear
• go to VIEW menu, MACROS tab, and click USE RELATIVE REFERENCES
• click RECORD MACRO
• enter name, description, assign shortcut key
• go to FORMULAS menu and click AUTOSUM
• press RETURN

when i test the macro out on columns containing the same amount of rows, it works fine. but when i run the macro on a column with more rows, it only totals the amount of rows contained in the original macro. see attached.

Notes:

• tab t1_2010 contains fewer rows than tab t2_2010.
• i recorded a macro on tab t1_2010 called TOTALS

please help.

thanks for reading.

best,

ian

[JBeaucaire]

How about this:

Please Login or Register  to view this content.

[ilundy77]

works beautifully! thanks so much.


best,

ian

[JBeaucaire]

Just for chuckles how about this...it will add all the totals for you all at once;

Please Login or Register  to view this content.