| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Review this if function please.....
I know it looks kind of bad but it is an IF function (7 of them) that reads
the value of a column in a worksheet that has 7 columns and puts it in a specific cell in another work sheet, not a big deal, but it has worked for me, but if two columns have the same cost code it will only bring in the info from the first column that has that same cost code, 2 are my questions first: can I make it any shorter? and is there a way of summing up the info of all the columns that have the same cost code it could be in two, three up two 7 columns? =IF(A4='Foreman''s DTC(1)'!$D$12,'Foreman''s DTC(1)'!$E$41,IF(A4='Foreman''s DTC(1)'!$F$12,'Foreman''s DTC(1)'!$G$41,IF(A4='Foreman''s DTC(1)'!$H$12,'Foreman''s DTC(1)'!$I$41,IF(A4='Foreman''s DTC(1)'!$J$12,'Foreman''s DTC(1)'!$K$41,IF(A4='Foreman''s DTC(1)'!$L$12,'Foreman''s DTC(1)'!$M$41,IF(A4='Foreman''s DTC(1)'!$N$12,'Foreman''s DTC(1)'!$O$41,IF(A4='Foreman''s DTC(1)'!$P$12,'Foreman''s DTC(1)'!$Q$41,"0"))))))) Thanks, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Review this if function please.....
what is the 14:14 and the 41:41 specifying? in my case should it be D12 or
should I use the 12:12? the cell from which I want the info is merged could that be a problem?..... this is how I got the formula written down in the cell and it gives me a #REF! error =INDEX('Foreman''s DTC(1)'!D12,0,MATCH(A4,'Foreman''s DTC(1)'!D12:Q12)*2+5) "Don Guillett" wrote: Modify this idea to suit from my test below =INDEX(Sheet15!14:14,0,MATCH($B$13,Sheet15!$D$12:$ P$12)*2+5) change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... I know it looks kind of bad but it is an IF function (7 of them) that reads the value of a column in a worksheet that has 7 columns and puts it in a specific cell in another work sheet, not a big deal, but it has worked for me, but if two columns have the same cost code it will only bring in the info from the first column that has that same cost code, 2 are my questions first: can I make it any shorter? and is there a way of summing up the info of all the columns that have the same cost code it could be in two, three up two 7 columns? =IF(A4='Foreman''s DTC(1)'!$D$12,'Foreman''s DTC(1)'!$E$41,IF(A4='Foreman''s DTC(1)'!$F$12,'Foreman''s DTC(1)'!$G$41,IF(A4='Foreman''s DTC(1)'!$H$12,'Foreman''s DTC(1)'!$I$41,IF(A4='Foreman''s DTC(1)'!$J$12,'Foreman''s DTC(1)'!$K$41,IF(A4='Foreman''s DTC(1)'!$L$12,'Foreman''s DTC(1)'!$M$41,IF(A4='Foreman''s DTC(1)'!$N$12,'Foreman''s DTC(1)'!$O$41,IF(A4='Foreman''s DTC(1)'!$P$12,'Foreman''s DTC(1)'!$Q$41,"0"))))))) Thanks, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Review this if function please.....
Try it the way I wrote it ONLY changing sheet name
BTW your sheet naming convention SUCKS. Try something like ForemansDTC! the 12:12 is row 12..................... and 41:41 is row 41......... from YOUR example change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) If all else fails you may send your workbook to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... what is the 14:14 and the 41:41 specifying? in my case should it be D12 or should I use the 12:12? the cell from which I want the info is merged could that be a problem?..... this is how I got the formula written down in the cell and it gives me a #REF! error =INDEX('Foreman''s DTC(1)'!D12,0,MATCH(A4,'Foreman''s DTC(1)'!D12:Q12)*2+5) "Don Guillett" wrote: Modify this idea to suit from my test below =INDEX(Sheet15!14:14,0,MATCH($B$13,Sheet15!$D$12:$ P$12)*2+5) change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... I know it looks kind of bad but it is an IF function (7 of them) that reads the value of a column in a worksheet that has 7 columns and puts it in a specific cell in another work sheet, not a big deal, but it has worked for me, but if two columns have the same cost code it will only bring in the info from the first column that has that same cost code, 2 are my questions first: can I make it any shorter? and is there a way of summing up the info of all the columns that have the same cost code it could be in two, three up two 7 columns? =IF(A4='Foreman''s DTC(1)'!$D$12,'Foreman''s DTC(1)'!$E$41,IF(A4='Foreman''s DTC(1)'!$F$12,'Foreman''s DTC(1)'!$G$41,IF(A4='Foreman''s DTC(1)'!$H$12,'Foreman''s DTC(1)'!$I$41,IF(A4='Foreman''s DTC(1)'!$J$12,'Foreman''s DTC(1)'!$K$41,IF(A4='Foreman''s DTC(1)'!$L$12,'Foreman''s DTC(1)'!$M$41,IF(A4='Foreman''s DTC(1)'!$N$12,'Foreman''s DTC(1)'!$O$41,IF(A4='Foreman''s DTC(1)'!$P$12,'Foreman''s DTC(1)'!$Q$41,"0"))))))) Thanks, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Review this if function please.....
solved
=IF(ISNA(MATCH($A4,'ForemansDTC(1)'!$12:$12,0)),"" ,INDEX('ForemansDTC(1)'!$41:$41,MATCH($A4,'Foreman sDTC(1)'!$12:$12)+1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Try it the way I wrote it ONLY changing sheet name BTW your sheet naming convention SUCKS. Try something like ForemansDTC! the 12:12 is row 12..................... and 41:41 is row 41......... from YOUR example change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) If all else fails you may send your workbook to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... what is the 14:14 and the 41:41 specifying? in my case should it be D12 or should I use the 12:12? the cell from which I want the info is merged could that be a problem?..... this is how I got the formula written down in the cell and it gives me a #REF! error =INDEX('Foreman''s DTC(1)'!D12,0,MATCH(A4,'Foreman''s DTC(1)'!D12:Q12)*2+5) "Don Guillett" wrote: Modify this idea to suit from my test below =INDEX(Sheet15!14:14,0,MATCH($B$13,Sheet15!$D$12:$ P$12)*2+5) change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... I know it looks kind of bad but it is an IF function (7 of them) that reads the value of a column in a worksheet that has 7 columns and puts it in a specific cell in another work sheet, not a big deal, but it has worked for me, but if two columns have the same cost code it will only bring in the info from the first column that has that same cost code, 2 are my questions first: can I make it any shorter? and is there a way of summing up the info of all the columns that have the same cost code it could be in two, three up two 7 columns? =IF(A4='Foreman''s DTC(1)'!$D$12,'Foreman''s DTC(1)'!$E$41,IF(A4='Foreman''s DTC(1)'!$F$12,'Foreman''s DTC(1)'!$G$41,IF(A4='Foreman''s DTC(1)'!$H$12,'Foreman''s DTC(1)'!$I$41,IF(A4='Foreman''s DTC(1)'!$J$12,'Foreman''s DTC(1)'!$K$41,IF(A4='Foreman''s DTC(1)'!$L$12,'Foreman''s DTC(1)'!$M$41,IF(A4='Foreman''s DTC(1)'!$N$12,'Foreman''s DTC(1)'!$O$41,IF(A4='Foreman''s DTC(1)'!$P$12,'Foreman''s DTC(1)'!$Q$41,"0"))))))) Thanks, |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Review this if function please.....
didn't work 100% it brings different info from different cells, some work
some don't. I will send you the example...thanks Don "Don Guillett" wrote: solved =IF(ISNA(MATCH($A4,'ForemansDTC(1)'!$12:$12,0)),"" ,INDEX('ForemansDTC(1)'!$41:$41,MATCH($A4,'Foreman sDTC(1)'!$12:$12)+1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Try it the way I wrote it ONLY changing sheet name BTW your sheet naming convention SUCKS. Try something like ForemansDTC! the 12:12 is row 12..................... and 41:41 is row 41......... from YOUR example change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) If all else fails you may send your workbook to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... what is the 14:14 and the 41:41 specifying? in my case should it be D12 or should I use the 12:12? the cell from which I want the info is merged could that be a problem?..... this is how I got the formula written down in the cell and it gives me a #REF! error =INDEX('Foreman''s DTC(1)'!D12,0,MATCH(A4,'Foreman''s DTC(1)'!D12:Q12)*2+5) "Don Guillett" wrote: Modify this idea to suit from my test below =INDEX(Sheet15!14:14,0,MATCH($B$13,Sheet15!$D$12:$ P$12)*2+5) change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... I know it looks kind of bad but it is an IF function (7 of them) that reads the value of a column in a worksheet that has 7 columns and puts it in a specific cell in another work sheet, not a big deal, but it has worked for me, but if two columns have the same cost code it will only bring in the info from the first column that has that same cost code, 2 are my questions first: can I make it any shorter? and is there a way of summing up the info of all the columns that have the same cost code it could be in two, three up two 7 columns? =IF(A4='Foreman''s DTC(1)'!$D$12,'Foreman''s DTC(1)'!$E$41,IF(A4='Foreman''s DTC(1)'!$F$12,'Foreman''s DTC(1)'!$G$41,IF(A4='Foreman''s DTC(1)'!$H$12,'Foreman''s DTC(1)'!$I$41,IF(A4='Foreman''s DTC(1)'!$J$12,'Foreman''s DTC(1)'!$K$41,IF(A4='Foreman''s DTC(1)'!$L$12,'Foreman''s DTC(1)'!$M$41,IF(A4='Foreman''s DTC(1)'!$N$12,'Foreman''s DTC(1)'!$O$41,IF(A4='Foreman''s DTC(1)'!$P$12,'Foreman''s DTC(1)'!$Q$41,"0"))))))) Thanks, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Review this if function please.....
12,0 works now
-- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... didn't work 100% it brings different info from different cells, some work some don't. I will send you the example...thanks Don "Don Guillett" wrote: solved =IF(ISNA(MATCH($A4,'ForemansDTC(1)'!$12:$12,0)),"" ,INDEX('ForemansDTC(1)'!$41:$41,MATCH($A4,'Foreman sDTC(1)'!$12:$12)+1)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Try it the way I wrote it ONLY changing sheet name BTW your sheet naming convention SUCKS. Try something like ForemansDTC! the 12:12 is row 12..................... and 41:41 is row 41......... from YOUR example change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) If all else fails you may send your workbook to my address below -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... what is the 14:14 and the 41:41 specifying? in my case should it be D12 or should I use the 12:12? the cell from which I want the info is merged could that be a problem?..... this is how I got the formula written down in the cell and it gives me a #REF! error =INDEX('Foreman''s DTC(1)'!D12,0,MATCH(A4,'Foreman''s DTC(1)'!D12:Q12)*2+5) "Don Guillett" wrote: Modify this idea to suit from my test below =INDEX(Sheet15!14:14,0,MATCH($B$13,Sheet15!$D$12:$ P$12)*2+5) change to your sheet name =INDEX(Sheet15!41:41,0,MATCH(a4,Sheet15!$D$12:$P$1 2)*2+5) -- Don Guillett Microsoft MVP Excel SalesAid Software "jcheko" wrote in message ... I know it looks kind of bad but it is an IF function (7 of them) that reads the value of a column in a worksheet that has 7 columns and puts it in a specific cell in another work sheet, not a big deal, but it has worked for me, but if two columns have the same cost code it will only bring in the info from the first column that has that same cost code, 2 are my questions first: can I make it any shorter? and is there a way of summing up the info of all the columns that have the same cost code it could be in two, three up two 7 columns? =IF(A4='Foreman''s DTC(1)'!$D$12,'Foreman''s DTC(1)'!$E$41,IF(A4='Foreman''s DTC(1)'!$F$12,'Foreman''s DTC(1)'!$G$41,IF(A4='Foreman''s DTC(1)'!$H$12,'Foreman''s DTC(1)'!$I$41,IF(A4='Foreman''s DTC(1)'!$J$12,'Foreman''s DTC(1)'!$K$41,IF(A4='Foreman''s DTC(1)'!$L$12,'Foreman''s DTC(1)'!$M$41,IF(A4='Foreman''s DTC(1)'!$N$12,'Foreman''s DTC(1)'!$O$41,IF(A4='Foreman''s DTC(1)'!$P$12,'Foreman''s DTC(1)'!$Q$41,"0"))))))) Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| review changes in workbook | Excel Discussion (Misc queries) | |||
| Months of review | Excel Worksheet Functions | |||
| i have a fax sent to me for review but i cant open its saying i n | New Users to Excel | |||
| Review commonly used add-ins | Excel Discussion (Misc queries) | |||
| Review Tool bar | Excel Discussion (Misc queries) | |||
Linear Mode
