Update formula criteria

[webbooo]

hello everyone,

I hve the following formaul in excel

=COUNTIF($A3,Sheet2!$A$14)+COUNTIF($A3,Sheet2!$B$14)+COUNTIF($A3,Sheet2!$C$14)+COUNTIF($A3,Sheet2!$D$14)+COUNTIF($A3,Sheet2!$E$14)+COUNTIF($A3,Sheet2!$F$14)

the problem I have is I would like to copy this formula into another column and change each reference to 14 to 15. I can do this manually but given I will potentially be coping the formula into 100's of cells each time doing it manually wouold take me ages.

I tried the obvious and changed the $A$14 TO $A14 but this wont change when moving formaul accross only when you move it down. The "$A" is OK being locked and the rest of the formula I'm ok with.

Does anyone have any suggestion, Its almost like each time the formula is copied the criteria cells need to add a digit.

any help would be mich appreciated, I'm not that familiar with vba but open to any suggestions

thanks for any assiatance

Me

 

[Harsh31412]

You can find $14 & replace it with $15

- copy the formula at the column you desire

- select the copied range

- press ctrl + h

- type $14 at find field and $15 at replace field

- press ok

This will replace 14 as 15 in one shot

 

[mmodroger]

=COUNTIF($A3,INDIRECT("Sheet2!$A$"&14+COLUMNS($A$1:A1)-1))

 

[webbooo]

Thanks for the response's

Harsh31412, that does work thank you. It does require manual intervention to enter the find/replace criteria and would like to be able to automate it as much as possible. Doing that 100+ times would be painfull

mmodroger, your didnt seem to return any value, just 0 which isnt correct? are you able to break this down maybe I'm doing something wrong?

as an example, my sheet has over 200 names on sheet 1, the formula i have going into D3 as below is
"B]=COUNTIF($A3,Sheet2!$A$14)+COUNTIF($A3,Sheet2!$B$14)+COUNTIF($A3,Sheet2!$C$14)+COUNTIF($A3,Sheet2!$D$14)+COUNTIF($A3,Sheet2!$E$14)+COUNTIF($A3,Sheet2!$F$14)[/B]" -

If I fill this formula down it returns a 1 in the cells that had a match, which work fine. So D6,D17,D11,D5,D16,D10 all return a 1, other cells return 0.

I now need to copy the formula to D4 and drill down. If I do it the results are the same as I need to change the criteria from Sheet2 A14 to Sheet2 A15 and then repeat the process for the next column etc

Sheet 1

A3:Dave
A4:Alex
A5:Same
A6:Roger
A7:Glen
A8:Mark
A9:Simon
A10:Michael
A11:Susan
A12:Romeo
A13:Nona
A14:Olivia
A15:Jack
A16:Alf
A17:Bev

Sheet2
A14:Roger B14:Bev C14:Susan D14:Same E14:Alf F14:Michael
A15:Glen B15:Susan C15:Mark D15:Simon E15:Jack F15:Olivia
etc
etc

I hope this clarifies it,

thanks again

 

[mmodroger]

try this and see if it works in your sheet.

Excel Workbook
	A	B	C	D	E
4	Dave			0	0
5	Alex			0	0
6	Same			1	0
7	Roger
8	Glen
9	Mark
10	Simon
11	Michael
12	Susan
13	Romeo
14	Nona
15	Olivia
16	Jack
17	Alf
18	Bev
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D4	=SUMPRODUCT(--(INDIRECT("Sheet2!$A$"&(14+COLUMNS($D$4:D4)-1)&":$F$"&(14+COLUMNS($D$4:D4)-1))=Sheet1!$A4))
D5	=SUMPRODUCT(--(INDIRECT("Sheet2!$A$"&(14+COLUMNS($D$4:D5)-1)&":$F$"&(14+COLUMNS($D$4:D5)-1))=Sheet1!$A5))
D6	=SUMPRODUCT(--(INDIRECT("Sheet2!$A$"&(14+COLUMNS($D$4:D6)-1)&":$F$"&(14+COLUMNS($D$4:D6)-1))=Sheet1!$A6))
E4	=SUMPRODUCT(--(INDIRECT("Sheet2!$A$"&(14+COLUMNS($D$4:E4)-1)&":$F$"&(14+COLUMNS($D$4:E4)-1))=Sheet1!$A4))
E5	=SUMPRODUCT(--(INDIRECT("Sheet2!$A$"&(14+COLUMNS($D$4:E5)-1)&":$F$"&(14+COLUMNS($D$4:E5)-1))=Sheet1!$A5))
E6	=SUMPRODUCT(--(INDIRECT("Sheet2!$A$"&(14+COLUMNS($D$4:E6)-1)&":$F$"&(14+COLUMNS($D$4:E6)-1))=Sheet1!$A6))

 

[webbooo]

mmodroger, that worked

thank you very much you are a genius, dunno how that formula actually works but it does the job

really appreciate your help

thankyou!

 

[mmodroger]

thanks for the feedback. Indirect function is a great formula and you should learn it sometimes