Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
Hi,
need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
"Tony V" wrote: Give a better example and maybe someone could help I'm not really sure what you need. Hi, need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
In Excel, I have 25 rows and 12 columns. I would like to add a row with a
function that queries six cells in the same row to determine which cell contains data. As a rule, only one cell of the six has data. The other cells are blank. The data can be numeric or alpha numeric. I simply want to querie the range, find the cell with data in it and display it. -- T--- "Mike" wrote: "Tony V" wrote: Give a better example and maybe someone could help I'm not really sure what you need. Hi, need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
If the value were always numeric, you could use:
=sum(A1:F1) if the value were always text, you could use: =a1&b1&c1&d1&e1&f1 But the first will fail if the single value is text (you'll see 0). The second will return text even if the value is a number. Maybe you could live with those restrictions? If you can't, then there are other formulas: =index(a1:f1,match(true,a1:f1<"",0)) or =if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) But this will fail if those six cells aren't contiguous. Tony V wrote: In Excel, I have 25 rows and 12 columns. I would like to add a row with a function that queries six cells in the same row to determine which cell contains data. As a rule, only one cell of the six has data. The other cells are blank. The data can be numeric or alpha numeric. I simply want to querie the range, find the cell with data in it and display it. -- T--- "Mike" wrote: "Tony V" wrote: Give a better example and maybe someone could help I'm not really sure what you need. Hi, need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
Mr. Peterson.
Thank you very much, the function code "=if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0)))" worked, it is exactly what I was looking for. Thank you very much and I appreciate your consult! Let me know if how I can repay you for your effort! Thank you. -- T--- "Dave Peterson" wrote: If the value were always numeric, you could use: =sum(A1:F1) if the value were always text, you could use: =a1&b1&c1&d1&e1&f1 But the first will fail if the single value is text (you'll see 0). The second will return text even if the value is a number. Maybe you could live with those restrictions? If you can't, then there are other formulas: =index(a1:f1,match(true,a1:f1<"",0)) or =if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) But this will fail if those six cells aren't contiguous. Tony V wrote: In Excel, I have 25 rows and 12 columns. I would like to add a row with a function that queries six cells in the same row to determine which cell contains data. As a rule, only one cell of the six has data. The other cells are blank. The data can be numeric or alpha numeric. I simply want to querie the range, find the cell with data in it and display it. -- T--- "Mike" wrote: "Tony V" wrote: Give a better example and maybe someone could help I'm not really sure what you need. Hi, need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
If you hang around the newsgroups and see a post that needs an answer and you
know it, post it. Or just help someone out in real life. Tony V wrote: Mr. Peterson. Thank you very much, the function code "=if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0)))" worked, it is exactly what I was looking for. Thank you very much and I appreciate your consult! Let me know if how I can repay you for your effort! Thank you. -- T--- "Dave Peterson" wrote: If the value were always numeric, you could use: =sum(A1:F1) if the value were always text, you could use: =a1&b1&c1&d1&e1&f1 But the first will fail if the single value is text (you'll see 0). The second will return text even if the value is a number. Maybe you could live with those restrictions? If you can't, then there are other formulas: =index(a1:f1,match(true,a1:f1<"",0)) or =if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) But this will fail if those six cells aren't contiguous. Tony V wrote: In Excel, I have 25 rows and 12 columns. I would like to add a row with a function that queries six cells in the same row to determine which cell contains data. As a rule, only one cell of the six has data. The other cells are blank. The data can be numeric or alpha numeric. I simply want to querie the range, find the cell with data in it and display it. -- T--- "Mike" wrote: "Tony V" wrote: Give a better example and maybe someone could help I'm not really sure what you need. Hi, need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find the right function code
Touche'
Will help out someone in real life and on your behalf! Ciao, buona fortuna -- T--- "Dave Peterson" wrote: If you hang around the newsgroups and see a post that needs an answer and you know it, post it. Or just help someone out in real life. Tony V wrote: Mr. Peterson. Thank you very much, the function code "=if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0)))" worked, it is exactly what I was looking for. Thank you very much and I appreciate your consult! Let me know if how I can repay you for your effort! Thank you. -- T--- "Dave Peterson" wrote: If the value were always numeric, you could use: =sum(A1:F1) if the value were always text, you could use: =a1&b1&c1&d1&e1&f1 But the first will fail if the single value is text (you'll see 0). The second will return text even if the value is a number. Maybe you could live with those restrictions? If you can't, then there are other formulas: =index(a1:f1,match(true,a1:f1<"",0)) or =if(counta(a1:f1)<1,"not just one value!",index(a1:f1,match(true,A1:f1<"",0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) But this will fail if those six cells aren't contiguous. Tony V wrote: In Excel, I have 25 rows and 12 columns. I would like to add a row with a function that queries six cells in the same row to determine which cell contains data. As a rule, only one cell of the six has data. The other cells are blank. The data can be numeric or alpha numeric. I simply want to querie the range, find the cell with data in it and display it. -- T--- "Mike" wrote: "Tony V" wrote: Give a better example and maybe someone could help I'm not really sure what you need. Hi, need to find the right function code ... In a separate cell, I want to display either a number or a text found in a single cell from a range of cells, its a one to one relationship, i.e. in cell C4 show the entry (either a text or a number) that may be in one cell from D4 through G4. I'm stumped ;-) Thanks -- T--- -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Find" function question - No code required | Excel Discussion (Misc queries) | |||
VBA code for find function (reference cell value) | Excel Worksheet Functions | |||
can't find custom function code | Excel Worksheet Functions | |||
backwards find function to find character in a string of text | Excel Programming | |||
Help with this Find code please | Excel Programming |