How to extract data from one sheet and bring it to another sheet?

Vane

How to cross reference a sheet to get the following information?

I have a sheet titled "Change Order Log" with the following columns. My overall goal was to sum up the "Approved Change Order Amount" by category.

Example: I used the following formula to come up with the sub-total for the "Regulatory Submissions" category.

=SUMIFS([Approved Change Order Amount.]:[Approved Change Order Amount.], Category:Category, CONTAINS("Regulatory Submissions", @cell), Status:Status, "Approved")

However, now I plan to create another sheet with two columns (category name and formula), as seen below:

I want to extract the data from the original sheet (aka "Change Order Log") and bring the sub-totals for each category into this new sheet. Is this possible? if so, which formula should I use?

Ryan Sides

@Vane

Hi Vane, you'll need to create two cross sheet references on your rainbow sheet (right click in any cell and choose "Manage References". Hit the create button, navigate to your first sheet, highlight the column "category" (make sure to highlight the entire column, not just the cell), name this one "Categories" and click the "insert Reference" button. Perform the same steps again, this time highlight the Approved Change Order Amount column, name this one "COs".

Now you can use these in a SUMIFS formula similar to the one you created previously.

=SUMIFS({COs}, {Categories}, HAS(@cell, "Regulatory Submissions")

Please let me know if that works the way you needed.

-Ryan

Ryan Sides

@Vane

Hi Vane, you'll need to create two cross sheet references on your rainbow sheet (right click in any cell and choose "Manage References". Hit the create button, navigate to your first sheet, highlight the column "category" (make sure to highlight the entire column, not just the cell), name this one "Categories" and click the "insert Reference" button. Perform the same steps again, this time highlight the Approved Change Order Amount column, name this one "COs".

Now you can use these in a SUMIFS formula similar to the one you created previously.

=SUMIFS({COs}, {Categories}, HAS(@cell, "Regulatory Submissions")

Please let me know if that works the way you needed.

-Ryan

Vane

@Ryan Sides THANK YOU! Your approach worked.

Ryan Sides

@Vane glad you got it working! Enjoy.

-Ryan