Using data in formulas from different worksheets

Question

I have two different worksheets that are tables with data that is listed by city. Each worksheet is a separate day. I want to calculate the difference in the two days of revenue. But some pages have a different amount of cities so they don’t necessarily line up to the same row. So New York might occupy the same row as Philadelphia on a different worksheet. Is there a way to match the names in column 1 and using that row of information before calculating the formula?

mtarler · Answer

Jaxtonv&nbsp;This can be done with a variety of functions: XLOOKUP, VLOOKUP, LOOKUP, a combination of INDEX and MATCH, or others.&nbsp; If you provide a sample workbook we can be a little more specific on how to use it, otherwise, just look at the help information about what values need to be passed to it.

jaxtonv · Answer

Here is a sample the sample of the spreadsheet. I want to compare the yesterday columns on each spreadsheet but the cities change order so are not in the same row between the worksheets.

mtarler · Answer

Jaxtonv&nbsp; 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).&nbsp; I also used table notation which might be a little harder for you to read if you aren't familiar with it.&nbsp; 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.&nbsp; 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/.&nbsp; Since I don't know all the details of what you need I'm trying to give you options.&nbsp;&nbsp;

jaxtonv · Answer

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?

mtarler · Answer

Jaxtonv&nbsp;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.&nbsp; But to answer your question, if the city or another column was guaranteed to be unique then you could simplify the formula slightly.&nbsp; Here is the formula now:&nbsp;=IFERROR([@YESTERDAY]-1/1/SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY],Table7[STATE],[@STATE],Table7[COUNTRY],[@COUNTRY]),"n/a")&nbsp;but if the CITY was unique then you could use:&nbsp;=IFERROR([@YESTERDAY]-1/1/SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY]),"n/a")&nbsp;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:&nbsp;&nbsp;=[@YESTERDAY]-SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY])&nbsp;or with the original formula checking city, state, and country so you don't have to tweak the city names, it would be:&nbsp;&nbsp;=[@YESTERDAY]-SUMIFS(Table7[YESTERDAY],Table7[CITY],[@CITY],Table7[STATE],[@STATE],Table7[COUNTRY],[@COUNTRY])&nbsp;&nbsp;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 Nameb) 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 stylec) 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&nbsp;

Forum Discussion

Using data in formulas from different worksheets

6 Replies

Resources