Formula skipping data

[Joey]

Hi all

My formula which is dragged down in 7500 cells picks up only certai
data when I do a search.
One search brings up all the data I'm looking for and another ref# wil
not even recognise the data in the column and returns a blank cell/s.
I have gone over the this formula and other formulas in the spreadshee
the whole morning and can't get or see anything different or wrong.

Does anyone know why this could be happening?



Many thanks
Joe

 

[Bob Phillips]

Not without seeing it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
post your formula and also the values in the cells which
are referenced by this formula

 

[Joey]

Thanks Frank

It's huge but here goes;

This is where the data should result and dragged down.
=IF(ROW()-2>COUNTIF('Sicknes
Report'!$A$2:$A$9500,$G$5),"",OFFSET(INDEX('Sicknes
Report'!$A$2:$A$9500,MATCH($G$5,'Sicknes
Report'!$A$2:$A$9500,0)),ROW()-3,11))

This is in 'Sickness Report', Column L (11 as above)
=IF((A2='Sickness with teams'!$G$5)*(H2:H9500='Sickness wit
teams'!$C$5),E2,"")
Where the data comes from

An idea of what is in the other columns in ;
A = Ref #
B = Area
C = Managers
D = Names
E = Dates
F = Hours
G = Month
H = Department
I, J, K = have formulas regarding other data.

Thanks
Joe

 

[Frank Kabel]

Hi
sounds like your formula does not find a match in these cases. Have you
checked that there's really amatch. e.g. different formtas (text
versuse numbers), etc.

 

[Joey]

I don't know what you mean by text versus numbers

 

[Frank Kabel]

Hi
as I do not know what kind of data in in your columns one common error
cause is that a column seems to contain numbers but they're actually
stored as text or vice versa.
Try checking manually if these cells match with a formula like
=A2='sheet1'!A1
to test if both cells are REALLY identical

 

[Joey]

Hi

I created a column with this formula to check the data in the othe
column.
They all come back positive.

What gets me is that it doesn't pick up all the data when the mont
changes so I can't figure out if it's a cell regarding the mont
dropdown cell or the range after it reaches a certain row.

I will have to scrap this workbook and start all over if I can't sor
this problem.

Thanks for the help anyway.
Joe

 

[Frank Kabel]

Hi
if you like email me your file
email: frank[dot]kabel[at]freenet[dot]de