Formula/Function Formatting Question

[cpeterson]

I need help figuring out the best way to set up this table I've been working on. I track credit card payments for customers that we hold payment info on file for at my company. I have each customer set up individually on their own sheet within the same workbook. I also have a summary sheet that provides an overview so I can give my boss something that's easy to understand at a glance. On this summary page, I want to have a formula that can look at the cell to the immediate left, and then pull information from a specific customer for a SUMIFS formula based on a date range. I have the SUMIFS formula all setup, but I want to be able to have a generic formula that I can copy and paste without having to edit it every time that knows where to look for the ranges used in the SUMIFS formula. Does anyone have any recommendations on what the best way to go about this? Thanks for all your help!

[#2]

@cpeterson , if source data for each customer is separated, you may transform such data to Excel tables (or, as variant, use dynamic named ranges). Each of such tables is named as customer name. In summary sheet you also have customer name which you may use with INDIRECT within SUMIFS to identify that customer table/range.

[#3]

@Sergei Baklan upon some more investigation on my part, I figured out what I want is a formula that takes place of the sum_range & criteria_range values within the SUMIFS formula. Here is a mockup of the spreadsheet I'm working with.

[#4]

@cpeterson , with your naming convention that could be like

=SUMIFS(
   INDIRECT(SUBSTITUTE([@[Customer Name]], CHAR(32),)&"Table" & "[[Amount ]]"),
   INDIRECT(SUBSTITUTE([@[Customer Name]], CHAR(32),)&"Table" & "[Date]"), ">=" & $D$3,
   INDIRECT(SUBSTITUTE([@[Customer Name]], CHAR(32),)&"Table" & "[Date]"), "<=" & $E$3)

Please see in first table attached