Forum Discussion
Using data in formulas from different worksheets
Jaxtonv In the attached I ended up using a SUMIFS() formula because there wasn't a single unique column to use for the lookup (i.e. there are 2x Gainsville locations). I also used table notation which might be a little harder for you to read if you aren't familiar with it. And lastly I added the IFERROR() and the 1/1/ to detect if nothing was found BUT if the lookup value is or could be 0 this 'trick' is not good. But then again if when they aren't on list you prefer to assume they are 0 then simply remove the IFERROR() and the 1/1/. Since I don't know all the details of what you need I'm trying to give you options.
Thank you so much. If I changed the duplicate city names to unique names how would it work in that case.
Also, use table 7 as the reference. How did you determine it was table 7?
Jaxtonv I don't know why you would bother at this point since that could be asking for errors (like in the workbook you attached you changed Gainsville to GainsvilleF and GainsvilleG on Sheet2 but not Sheet1. But to answer your question, if the city or another column was guaranteed to be unique then you could simplify the formula slightly. Here is the formula now:
=IFERROR([@YESTERDAY]-1/1/SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY],Table7[STATE],[@STATE],Table7[COUNTRY],[@COUNTRY]),"n/a")but if the CITY was unique then you could use:
=IFERROR([@YESTERDAY]-1/1/SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY]),"n/a")or you could replace SUMIFS with SUMIF or VLOOKUP or one of the other LOOKUP functions.
Similarly, if you want to assume that if the CITY doesn't exist on the previous list you should use 0 (zero) then it can become:
=[@YESTERDAY]-SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY])or with the original formula checking city, state, and country so you don't have to tweak the city names, it would be:
=[@YESTERDAY]-SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY],Table7[STATE],[@STATE],Table7[COUNTRY],[@COUNTRY])and as for how I determined it is named Table7 there are a couple ways:
a) click anywhere in the table and on the ribbon click 'Table Design' and then on the left end of the ribbon is shows Table Name
b) when you are creating a formula you can click on a header in the table and it will automatically insert that table column in table reference style
c) if you already have an idea of what the table name might be, you can start to type it while entering a formula and that name will be listed as an option to autocomplete
Thank you very much it's a huge help