Look-up tables in Excel

  questions4u 15:08 24 Nov 09
Locked

I have inherited a multi-worksheet excel file to record staff holidays/sickness etc. The first worksheet is for the data entry by day (the Year sheet) and each member of staff then has a worksheet devoted to them to summarise their year to date info.

I cannot find the cells referred to in the existing formulae. The formula is: =SUMIF('Year Sheet'!C:C,$G$1,'Year Sheet'!D:D)

These references C:C and D:D do not exist in the Year sheet and I presume they are some sort of hidden look-up table, but how do I find them
and correct them?

So for such a long question and I hope it makes sense. I and many others have spent hours trying to crack this one!

  VoG II 15:18 24 Nov 09

'Year Sheet'!C:C refers to the whole of column C on the Year Sheet worksheet.

That formula sums Column D of Year Sheet where column C of Year sheet matches G1 of the sheet that holds the formula.

  questions4u 16:26 24 Nov 09

That's fantastic (and wonderfully succinct!), now it all makes sense.

cheers!

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5