Need help with formula

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

This is what im using & i keep getting a REF error C5 is on a total different
worksheet & im looking for numbers in the other 52 worksheets to match with C5

EXAMPLE the 52 worksheets will be clock # entered for overtime if worked in
columns AR,AS,AT,AV,AW,AX,AZ,BA,BB & the sheet im working on C column will
have clock # for people who have employment here. E, F column on same
worsheet is what im using TO LET ME KNOW if refused or worked overtime so
hours can be calculated. I have tried a lot of different combinations but
can,t get it to work

=IF('Week 1:Week 52'!AR6=C5,($C5),"")
 
Mike said:
This is what im using & i keep getting a REF error C5 is on a total different
worksheet & im looking for numbers in the other 52 worksheets to match with C5

EXAMPLE the 52 worksheets will be clock # entered for overtime. If worked data is entered in
columns AR,AS,AT,AV,AW,AX,AZ,BA,BB & the sheet im working on is C column & will
have clock # for people who have employment here. E, F column on same
worsheet is what im using TO LET ME KNOW if refused or worked overtime so
hours can be calculated. I have tried a lot of different combinations but
can,t get it to work
why won,t this work
=IF('Week 1:Week 52'!AR6=C5,($C5),"")
Click to expand...
 
It doesn't work that way, so pointless getting worked up over it. I'd suggest
that you use INDIRECT to quickly extract the necessary data from all 52
source sheets
In a summary sheet,
In B1 across list the actual source sheetnames: Week 1, Week 2, etc (just
drag B1 across)
In A2 down list all the cell refs of interest, eg: AR6
Then place this in B2: =INDIRECT("'"&B$1&"'!"&$A2)
Copy B2 across/fill down to extract the desired data from all 52 sheets in a
single swift strike. You can then formulate easily whatever-it-is based on
the extracts in this summary sheet in empty cols to the right. Any joy? hit
the YES below
 
Just in case somehow the earlier response to your original posting didn't
get through to you, here it is again, as a reply to your 2nd, self-post. You
should engage all responders who care enough about your query to plonk their
thoughts for you. Ah, well ..

----------------------------------------------------------------
It doesn't work that way, so pointless getting worked up over it. I'd
suggest
that you use INDIRECT to quickly extract the necessary data from all 52
source sheets

In a summary sheet,

In B1 across list the actual source sheetnames:
Week 1, Week 2, etc (just drag B1 across)
In A2 down list all the cell refs of interest, eg: AR6

Then place this in B2: =INDIRECT("'"&B$1&"'!"&$A2)
Copy B2 across/fill down to extract the desired data from all 52 sheets in a
single swift strike. You can then formulate easily whatever-it-is based on
the extracts in this summary sheet in empty cols to the right, etc
 
Back
Top