Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
I read to post on how to put an OR function inside and IF function. I can't
get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
Put this array formula in A1:E1, for example, on sheet1 and copy down
through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
Thank you, however, the 3 underscores in the data sheet doesn't turn out a
TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
I'm sorry. I just tried it again and it worked perfectly. THANK YOU!
One more fine tuning item. In the rows that contain "___" in column F, the only column that has data is column A, the others columns in these rows pull zeros into the summary sheet. Keeping in mind I need all the columns for the rows with a "c" in column F, is there an easy way to make the zeros in the rwos with a "___" in column F appear blank? I can say it in words, but I fele like the formula would get pretty complex. "veggies27" wrote: Thank you, however, the 3 underscores in the data sheet doesn't turn out a TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
I have one other glitch becuase of how I use the sheet. I need the ability to
insert rows in my data sheets and have them fall into place in the summary sheet. This worked out before since I had the same array-entered formula in the entire 5 columns x 520 rows array in the summary sheet. I would only end up losing the bottom rows, which I can compensate by having the same formula in 550 rows in the data sheet and filtering out the blank rows until they are needed when I add 30 more rows. With the formula you suggested, when I add in a row 15 (for instance) in the data sheet, because the formula for the summary sheet in specific for each row, it doesn;t update the whole summart sheet, in only leaves out that new row. The auto formulas shift and in one row it will say =IF(OR(datasheet!F14="c",datasheet!F14="___"),data sheet!A14:E14,"") and the next will say =IF(OR(datasheet!F16="c",datasheet!F16="___"),data sheet!A16:E16,"") Does this make sense? I really appreciate your help. "veggies27" wrote: I'm sorry. I just tried it again and it worked perfectly. THANK YOU! One more fine tuning item. In the rows that contain "___" in column F, the only column that has data is column A, the others columns in these rows pull zeros into the summary sheet. Keeping in mind I need all the columns for the rows with a "c" in column F, is there an easy way to make the zeros in the rwos with a "___" in column F appear blank? I can say it in words, but I fele like the formula would get pretty complex. "veggies27" wrote: Thank you, however, the 3 underscores in the data sheet doesn't turn out a TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
This array formula may work for you. This will make columns with no entry be
the empy string. =IF(OR(datasheet!F1="c",datasheet!F1="___"),IF(dat asheet!A1:E1<"",datasheet!A1:E1,""),"") Tyro "veggies27" wrote in message ... I'm sorry. I just tried it again and it worked perfectly. THANK YOU! One more fine tuning item. In the rows that contain "___" in column F, the only column that has data is column A, the others columns in these rows pull zeros into the summary sheet. Keeping in mind I need all the columns for the rows with a "c" in column F, is there an easy way to make the zeros in the rwos with a "___" in column F appear blank? I can say it in words, but I fele like the formula would get pretty complex. "veggies27" wrote: Thank you, however, the 3 underscores in the data sheet doesn't turn out a TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
This works great to solve that problem, however it doesn't help with the
problem of inserting rows in the middle in the datasheet. Did you have a chance to look at that? "Tyro" wrote: This array formula may work for you. This will make columns with no entry be the empy string. =IF(OR(datasheet!F1="c",datasheet!F1="___"),IF(dat asheet!A1:E1<"",datasheet!A1:E1,""),"") Tyro "veggies27" wrote in message ... I'm sorry. I just tried it again and it worked perfectly. THANK YOU! One more fine tuning item. In the rows that contain "___" in column F, the only column that has data is column A, the others columns in these rows pull zeros into the summary sheet. Keeping in mind I need all the columns for the rows with a "c" in column F, is there an easy way to make the zeros in the rwos with a "___" in column F appear blank? I can say it in words, but I fele like the formula would get pretty complex. "veggies27" wrote: Thank you, however, the 3 underscores in the data sheet doesn't turn out a TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
That can be easily handled. When you insert rows in the data sheet, select
both the data and summary sheets, insert the row(s) and then on the summary sheet, (make sure only the summary sheet is selected) copy the fomula above the inserted row(s) to the inserted row(s). Tyro "veggies27" wrote in message ... I have one other glitch becuase of how I use the sheet. I need the ability to insert rows in my data sheets and have them fall into place in the summary sheet. This worked out before since I had the same array-entered formula in the entire 5 columns x 520 rows array in the summary sheet. I would only end up losing the bottom rows, which I can compensate by having the same formula in 550 rows in the data sheet and filtering out the blank rows until they are needed when I add 30 more rows. With the formula you suggested, when I add in a row 15 (for instance) in the data sheet, because the formula for the summary sheet in specific for each row, it doesn;t update the whole summart sheet, in only leaves out that new row. The auto formulas shift and in one row it will say =IF(OR(datasheet!F14="c",datasheet!F14="___"),data sheet!A14:E14,"") and the next will say =IF(OR(datasheet!F16="c",datasheet!F16="___"),data sheet!A16:E16,"") Does this make sense? I really appreciate your help. "veggies27" wrote: I'm sorry. I just tried it again and it worked perfectly. THANK YOU! One more fine tuning item. In the rows that contain "___" in column F, the only column that has data is column A, the others columns in these rows pull zeros into the summary sheet. Keeping in mind I need all the columns for the rows with a "c" in column F, is there an easy way to make the zeros in the rwos with a "___" in column F appear blank? I can say it in words, but I fele like the formula would get pretty complex. "veggies27" wrote: Thank you, however, the 3 underscores in the data sheet doesn't turn out a TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR function in array-entered IF function
Is there any way to handle this without having to go that route? I'm setting
this up for several people to use, they will be the ones inserting rows, and I have a feeling that they will not get that right ever. "Tyro" wrote: That can be easily handled. When you insert rows in the data sheet, select both the data and summary sheets, insert the row(s) and then on the summary sheet, (make sure only the summary sheet is selected) copy the fomula above the inserted row(s) to the inserted row(s). Tyro "veggies27" wrote in message ... I have one other glitch becuase of how I use the sheet. I need the ability to insert rows in my data sheets and have them fall into place in the summary sheet. This worked out before since I had the same array-entered formula in the entire 5 columns x 520 rows array in the summary sheet. I would only end up losing the bottom rows, which I can compensate by having the same formula in 550 rows in the data sheet and filtering out the blank rows until they are needed when I add 30 more rows. With the formula you suggested, when I add in a row 15 (for instance) in the data sheet, because the formula for the summary sheet in specific for each row, it doesn;t update the whole summart sheet, in only leaves out that new row. The auto formulas shift and in one row it will say =IF(OR(datasheet!F14="c",datasheet!F14="___"),data sheet!A14:E14,"") and the next will say =IF(OR(datasheet!F16="c",datasheet!F16="___"),data sheet!A16:E16,"") Does this make sense? I really appreciate your help. "veggies27" wrote: I'm sorry. I just tried it again and it worked perfectly. THANK YOU! One more fine tuning item. In the rows that contain "___" in column F, the only column that has data is column A, the others columns in these rows pull zeros into the summary sheet. Keeping in mind I need all the columns for the rows with a "c" in column F, is there an easy way to make the zeros in the rwos with a "___" in column F appear blank? I can say it in words, but I fele like the formula would get pretty complex. "veggies27" wrote: Thank you, however, the 3 underscores in the data sheet doesn't turn out a TRUE for the formula. I switched it to "1" just to check and see if it was the ___ that was the porblem and still nothing is carried over for the second part of the OR statement. However, now it doesn't carry over all the data, only the lines with "c". "Tyro" wrote: Put this array formula in A1:E1, for example, on sheet1 and copy down through row 520. I changed the test for "1" to "___", 3 underscores as this is what you said you want. =IF(OR(datasheet!F1="c",datasheet!F1="___"),datash eet!A1:E1,"") Tyro "veggies27" wrote in message ... I read to post on how to put an OR function inside and IF function. I can't get it to work when I try to put an OR function into my array-entered IF function. This is what I have now: =IF(OR('datasheet'!F1:F520="c",'datasheet!F1:F520= "1"),'datasheet!A1:E520,"") I'd like it to only bring the A:E data over to the summary sheet if the F in the row of the datasheet is "c" or "___"(three underscores)...however it is moving in all my data. Can anyone tell me where I made a mistake, or if this is just not poossible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
You've entered too many arguments for this function | Excel Discussion (Misc queries) | |||
Array Function | Excel Worksheet Functions | |||
Function to not display until data entered | Excel Discussion (Misc queries) | |||
Is there an array function or something like it? | Excel Worksheet Functions | |||
need some help with an array function | Excel Discussion (Misc queries) |