How can I check a 4 column range for a particular number.

[jwongsf]

HELP Hi All,
I would like to check a 4 column range to see if a particular number has been added to a range of number.
this is the formula that I wrote but it does not test beyond the 2nd column.


=IF(OR((MATCH(3,COLUMN_D)),(MATCH(3,column_c)),(MATCH(3,column_b)),(MATCH(3,Column_a))),3,"")

Does anyone have an different way of check for this?

James
Thanks

[martindwilson]

using match

Please Login or Register  to view this content.

or using countif

Please Login or Register  to view this content.

[Domenic]

Or, if your columns are contiguous, as your formula suggest...

=IF(COUNTIF(A:D,3),3,"")

Hope this helps!

[jwongsf]

Can this work between worksheets?

[shg]

No. If those are named ranges all on different sheets, then use


=IF( COUNTIF(Column_A, 3) + COUNTIF(Column_B, 3) + COUNTIF(Column_C, 3) + COUNTIF(Column_D, 3), 3, "")

[jwongsf]

Thanks once again, has anyone incooperated this into either a vlookup or used Index/match as the first variable?

[shg]

has anyone incooperated this into either a vlookup or used Index/match as the first variable?

What are you trying to do? The formula tells you if the number 3 is in any of those ranges, which is what I thought you wanted ...

[jwongsf]

I was using this feature on one worksheet, with data
Work sheet one
from column d3 d4 d5 d8
Employee Name Employee ID Title hours
john smith 123456 7345 8.0
joe gray 654212 7222 16.0


in the next worksheet(2) is would copy the information based on which line that you wanted to change their hours, say that you wanted to charge company1 with 5 hours company 2 1.5 hours and company-3 1.5 hours
So to make that this is the information that you want to use I would place a "1" in column c3, b3 a "2" and a "4" in column a3 (if I wanted to repeat this information in line 4 (24), etc
In worksheet 2
I want the data to start with the first select 1 number say in line 21 it would then populate the information in d21" john smith, I can get this done by using one column but not multi columns

[jwongsf]

I was using this feature on one worksheet, with data
Work sheet one
from column d3 d4 d5 d8
Employee Name Employee ID Title hours
john smith 123456 7345 8.0
joe gray 654212 7222 16.0


in the next worksheet(2) is would copy the information based on which line that you wanted to change their hours, say that you wanted to charge company1 with 5 hours company 2 1.5 hours and company-3 1.5 hours
So to make that this is the information that you want to use I would place a "1" in column c3, b3 a "2" and a "4" in column a3 (if I wanted to repeat this information in line 4 (24), etc
In worksheet 2
I want the data to start with the first select 1 number say in line 21 it would then populate the information in d21" john smith, I can get this done by using one column but not multi columns

[jwongsf]

here is the file that I am working on, I am currently using a lookup to populate the information, but would like to be able to assisgn a line number on the left side of the data worksheet like I did in the example. however to repeat a data, I would have to skip a line number and go to the data entry page and in the left of line to where I would like the data to appear, use the line number that I would like to copy, and I could any of the prior number that I have assigned.
Please let me know if you can think of another why to approach this problem.


Thank you once again for your assistance
James

[Domenic]

Unclear... Can you elaborate?

[jwongsf]

HI,
See the attached file,
ON the "DATA download" this information is copy and paste into this work sheet, IN column D, is where you would select the line information that you would like to make changes to. The numbers do not have to be in order but must be sequential. The problem was that if you wanted to use the same line of information, how would you be able to do this.
One thought was to allow you to use column c to repeat the line again, specifying on what line you would like this information to be place in the DATA entry worksheet. However I was unable to accomplish this. What I did was to allow you to specfic the line you wanted to repeat in column A, in the DATA entry sheet. and by skipping a number on the prior sheet. so the example shows that I skip the number 2 because in the second line

[jwongsf]

Row 4, column A I place a "1" to denote to repeat the first line into line 2. I would also like to have it Check to see that the same number.
I would like to be able to use this lines selection on the data sheet to select the repeat of the number in column a,b,c,and d thus allowing you to repeat the information 4 times.