Forum Discussion
Referencing across sheets using row numbers
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
- 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
