Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random search
If column K3 is filled (date) and/or column AC3 if filled (date), I want the
text in column X3 to autopopulate in column AE3 as long as in matches the mm/yyyy that I have entered in AE1. Column X will always keep its data. Column AE can changed based on the date entered in AE1. AE1 12/2005 K3 X3 AC3 AE3 12/15/2005 Unrepairable 12/20/2005 Unrepairable 12/16/2005 Serviceable 12/21/2005 Serviceable |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random search
Column X will have a variety of text in it. Not just the two examples I gave.
"roy.okinawa" wrote: If column K3 is filled (date) and/or column AC3 if filled (date), I want the text in column X3 to autopopulate in column AE3 as long as in matches the mm/yyyy that I have entered in AE1. Column X will always keep its data. Column AE can changed based on the date entered in AE1. AE1 12/2005 K3 X3 AC3 AE3 12/15/2005 Unrepairable 12/20/2005 Unrepairable 12/16/2005 Serviceable 12/21/2005 Serviceable |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random search
Hi Roy,
trouble with IF again ? Are you saying that AC3 (only) has to match the month and year in AE1, or can EITHER K3 OR AC3 match with AE1? Can you guarantee that column X will contain some text (i.e. not empty)? Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random search
Hi Pete,
Thanks for the assist. I'm getting better, but not there yet. K3 or AC3 can match. K3 is the open date and AC3 is the closed date. They may or may not open/close in the same month. That is why I want it to search for the month match in AE1. Column X may be empty if no remarks are required. "Pete" wrote: Hi Roy, trouble with IF again ? Are you saying that AC3 (only) has to match the month and year in AE1, or can EITHER K3 OR AC3 match with AE1? Can you guarantee that column X will contain some text (i.e. not empty)? Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random search
Roy,
enter the following formula in cell AE3: =IF(X3="","", IF(OR( (AND(MONTH(AC3)=MONTH(AE$1),YEAR(AC3)=YEAR(AE$1))) , (AND(MONTH(K3)=MONTH(AE$1),YEAR(K3)=YEAR(AE$1)) )),X3,"")) As before, all one formula - I've just split it here to make it easier to read. If you copy it down it will continue to reference AE1, but use the values on other rows to determine the outcome. Hope this helps, Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random search
Pete,
When I go to save the formula, it keeps saying there is an error in the formula that was typed. It then highlights the last X3 in the formula. "Pete" wrote: Roy, enter the following formula in cell AE3: =IF(X3="","", IF(OR( (AND(MONTH(AC3)=MONTH(AE$1),YEAR(AC3)=YEAR(AE$1))) , (AND(MONTH(K3)=MONTH(AE$1),YEAR(K3)=YEAR(AE$1)) )),X3,"")) As before, all one formula - I've just split it here to make it easier to read. If you copy it down it will continue to reference AE1, but use the values on other rows to determine the outcome. Hope this helps, Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Random Sampling | Excel Discussion (Misc queries) | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
FAQ Spreadsheet with search function | Excel Discussion (Misc queries) |