Linking two sheets to create a 3rd master

Leann Gibson

Can you link to two sheets into a 3rd to create a master sheet? Trying to create a master sheet that totals cells from two different ones.

Paul Newcome

You would have to pre-populate the 3rd sheet with the data to match on and then use formulas with cross sheet references.

Are you able to provide some screenshots for reference?

Leann Gibson

Here is a screen shot, imagine this is two different sheets. I want to combine to the data from these into one master sheet. The data is the same, one just shows the Decisions and the other shows the Advice. I want a 3rd sheet that gives total program (first column) by quarter.

Paul Newcome

You are going to have to replicate the sheet (first column and Then the top portion where you have each of the quarters listed out). From there you would need to use formulas with cross sheet references such as

=INDEX({Sheet 1 Q1 through Q4 Columns}, MATCH($[First Column]@row, {Sheet 1 First Column}, 0), MATCH(Q1$1, {Sheet 1 Row W/Qs listed}, 0)) + INDEX({Sheet 2 Q1 through Q4 Columns}, MATCH($[First Column]@row, {Sheet 2 First Column}, 0), MATCH(Q1$1, {Sheet 2 Row W/Qs listed}, 0))

You would insert this formula in the the top right cell of the table where you want the totals to be populated then dragfill it down and over.