Look-up tables in Excel

  questions4u 15:08 24 Nov 2009
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 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.

cheers!

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 ?