Referencing across sheets using row numbers

Copper Contributor

Dear All,

 
Sheet 1 summarises Values listed in Sheet 2.
Sheet 1 has categories listed in Column A , and a formula in Column B
Sheet 2 contains a list of expenses that I want add up in Sheet 1, by using the Row numbers in Sheet 1 as a reference, rather than use the names of the categories.
 So Sheet 2 has numbers to represent rows in sheet 1 listed in Column A , and the amounts in Column B, ie
8     $500
The formula in Sheet 1 , Column B will be something like :
=SUMIF(Sheet2!A2:A1000,"Sheet1!ROW()",(Sheet2!B2:B1000))
So that in Sheet 1, Row 8, it would summarise all values from Sheet 2 that have an 8 in the reference column.
But it's not working. Should I use and INDIRECT in there ?
Any suggestions would be much appreciated  - I tried without the Sheet1! in front of ROW(),
I also tried to fixate the row numbers in Sheet 1 in a column so that it could take it from there, but that's not working either. I hope you don't think I am complicating my life unnecessarily - 
Thanks, Ben
2 Replies
Have you considered using a pivot table? Very flexible and no formulas required at all.

Dear Jan,

Thank you for your response.

I wasn't aware of pivot tables, I will try it !

 

Best regards,

Ben