Look-up tables in Excel

  questions4u 15:08 24 Nov 2009

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 2009

'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 2009

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


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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?