Look-up tables in Excel

  questions4u 15:08 24 Nov 09

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.


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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…