# Compare value from one cell with other sheets with different names

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

=INDIRECT("'Sheet"&ROW()-1&"'!"&\$A\$54)

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

• 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