Reference Other Sheet

DavoBricko

I have a SUMIFS formula that works fine in a Sales Pipeline sheet.  I'd like to set up a new sheet with formulas just to collate data and would like the sumif to reference the Sales pipeline.

What do i need to prefix the formula below with to read from the Sales pipeline called NEW Enq log

=SUMIFS([Project Value]:[Project Value], [Sales Person]:[Sales Person], "David Bricknell", [Enq Status]:[Enq Status], "Live")

Chris McKay

Hi DavoBricko,

You don't need to prefix the formula with anything.

If you click on your formula, you'll be given the option to "Reference Another Sheet" via a hyperlink. This, in turn, opens another window allowing you to specify:

• the sheet you'd like to reference
• the cells, rows or columns you would like to reference
• a name for the range you are referencing

Once you've done this, the new range will appear in your formula (wherever the cursor was when you began this process) inside curly braces like this: {RANGE}

Not being familiar with the structure of your sheets, I don't really know enough about what you're trying to achieve to comment with 100% certainty, but I'd say your formula would end up looking something like:

=SUMIFS([Project Value]:[Project Value], [Sales Person]:[Sales Person], "David Bricknell", {Enq Status}, "Live")

Where [Project Value] and [Sales Person] are columns on your new sheet and {Enq Status} is a named range (the whole [Enq Status] column) on your NEW Enq log sheet.

DavoBricko

That helped a lot!

I was over complicating it.  Many thanks

Chris McKay

Happy to help!