i have a workbook, where i create one sheet per employee, the sheet is named after the employee. The sheet has two columns Variable and Values.

The Variables will differ for departments

I create a summary sheet where i will list all the variables in one column and sum the values from the employees Logically it goes like this

IF Summary.c16 = (andy.c16) then count = count + 1 IF Summary.c16 = bob.c16 then count = count +1

Like this the value in C16 is compared with value of all sheets

for sum part i can use SUM(first:last!AD16) but comparing one value to all cells is what i got stumped I was suggested to use Indirect


but here the sheet names are to be like Sheet1, sheet 2 ... but in my case sheet names are different

Please help and guide me

  • please clarify your question: do you use Excel? Do you want to do it with formula or also use VBA (your IF statement suggest VBA)? Are you interested in values or just want to count in how many sheets each variable occurs? – Máté Juhász Apr 10 '15 at 7:21
  • thanks for the reply I use Excel only, i dont want to use VBA as i have littel knowledge about it – Sarav Apr 10 '15 at 7:29
  • thanks for the reply I use Excel only, i dont want to use VBA as i have littel knowledge about it . i would like to do this using formula only I have used the IF ,only to explain what i want to do. This could be the IF function in Excel . The requirement is for both, i will sum as well as count. I could use a function like IF( C16 =andy.c16,sam.c16....., Sum(First:last!c16) – Sarav Apr 10 '15 at 7:35
  • It's possible to solve without VBA but I wouldn't do it: - Solution with formulas only: you need to enter as many Index() functions as many sheets your have manually entering name of each sheet. - Solution with VBA: collect all your data in one sheet, adding one column with sheet name and create your summary with a pivot table. – Máté Juhász Apr 10 '15 at 8:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.