Home

Referencing across sheets using row numbers

Highlighted
Benjamin Storch
New 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies