Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
In one section of my form there are 2 columns: A and B. Cell range B1:B7
contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Leslie,
Try this in C1 =VLOOKUP("x",A1:B7,2,FALSE) If you have more than 1 x in column A it will rturn the first. Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
To get rid of the error with no x in column A
Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
The formula would be helpful <g
=IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Mike - Thank you for responding. I have entered the formula you recommend.
Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Mike - Thank you for responding. I have entered the formula you recommend.
Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Most odd, it works perfectly for me are you sure you pasted the formula
exacly as written? You should get 'Nothing selected' if you don't have an x in A1 to A7 What appears in C1 when you put an x in one of the A cells? Mike "Leslie" wrote: Mike - Thank you for responding. I have entered the formula you recommend. Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
I took your advise and repeated the steps. I carefully pasted in the formula
and "Nothing Selected" does display when there is no "x" in cells A1:A7, but nothing appears in the C1 cell when I type an "x" in one of the A1:A7 cells. C1 changes to completely blank. I wonder if this fact is worth mentioning: the cells in column A and B are actually merged cells, for example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. Would this change the composition of the formula? Thanks for your patience, Mike. Would it be helpful for you to see a copy of my form firsthand? "Mike H" wrote: Most odd, it works perfectly for me are you sure you pasted the formula exacly as written? You should get 'Nothing selected' if you don't have an x in A1 to A7 What appears in C1 when you put an x in one of the A cells? Mike "Leslie" wrote: Mike - Thank you for responding. I have entered the formula you recommend. Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Leslie,
Yes that would be a significant ommision but now you have me more confused example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. If A1 is merged A1:D1 then C1 doesn't exist but there we are. Setting that aside the formula can go in any cell and from what you have told me this should work. =IF(ISNA(VLOOKUP("x",A1:E20,5,FALSE)),"Nothing Selected",VLOOKUP("x",A1:E20,5,FALSE)) To check we are addressing the correct cells select one and look in the top left corner of the spreadsheet to get the address and adjust accordingly. As an aside there are no circumstances under which I would merge cells. Apart from an attempt to make a sheet look pretty I know of no patctical reason for doing so and if you do it then it will eventually bite you on the leg. If you want pretty things then use Powerpoint. Mike "Leslie" wrote: I took your advise and repeated the steps. I carefully pasted in the formula and "Nothing Selected" does display when there is no "x" in cells A1:A7, but nothing appears in the C1 cell when I type an "x" in one of the A1:A7 cells. C1 changes to completely blank. I wonder if this fact is worth mentioning: the cells in column A and B are actually merged cells, for example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. Would this change the composition of the formula? Thanks for your patience, Mike. Would it be helpful for you to see a copy of my form firsthand? "Mike H" wrote: Most odd, it works perfectly for me are you sure you pasted the formula exacly as written? You should get 'Nothing selected' if you don't have an x in A1 to A7 What appears in C1 when you put an x in one of the A cells? Mike "Leslie" wrote: Mike - Thank you for responding. I have entered the formula you recommend. Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Thank you for your help, Mike. The merged cells was the problem. I went
back and unmerged the cells, making each of my columns only one cell wide, and the formula worked perfectly! Thank you again very much for your assistance. I sincerely appreciate your assistance and your time! Leslie "Mike H" wrote: Leslie, Yes that would be a significant ommision but now you have me more confused example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. If A1 is merged A1:D1 then C1 doesn't exist but there we are. Setting that aside the formula can go in any cell and from what you have told me this should work. =IF(ISNA(VLOOKUP("x",A1:E20,5,FALSE)),"Nothing Selected",VLOOKUP("x",A1:E20,5,FALSE)) To check we are addressing the correct cells select one and look in the top left corner of the spreadsheet to get the address and adjust accordingly. As an aside there are no circumstances under which I would merge cells. Apart from an attempt to make a sheet look pretty I know of no patctical reason for doing so and if you do it then it will eventually bite you on the leg. If you want pretty things then use Powerpoint. Mike "Leslie" wrote: I took your advise and repeated the steps. I carefully pasted in the formula and "Nothing Selected" does display when there is no "x" in cells A1:A7, but nothing appears in the C1 cell when I type an "x" in one of the A1:A7 cells. C1 changes to completely blank. I wonder if this fact is worth mentioning: the cells in column A and B are actually merged cells, for example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. Would this change the composition of the formula? Thanks for your patience, Mike. Would it be helpful for you to see a copy of my form firsthand? "Mike H" wrote: Most odd, it works perfectly for me are you sure you pasted the formula exacly as written? You should get 'Nothing selected' if you don't have an x in A1 to A7 What appears in C1 when you put an x in one of the A cells? Mike "Leslie" wrote: Mike - Thank you for responding. I have entered the formula you recommend. Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a formula
Your most welcome and thanks for the feedback
"Leslie" wrote: Thank you for your help, Mike. The merged cells was the problem. I went back and unmerged the cells, making each of my columns only one cell wide, and the formula worked perfectly! Thank you again very much for your assistance. I sincerely appreciate your assistance and your time! Leslie "Mike H" wrote: Leslie, Yes that would be a significant ommision but now you have me more confused example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. If A1 is merged A1:D1 then C1 doesn't exist but there we are. Setting that aside the formula can go in any cell and from what you have told me this should work. =IF(ISNA(VLOOKUP("x",A1:E20,5,FALSE)),"Nothing Selected",VLOOKUP("x",A1:E20,5,FALSE)) To check we are addressing the correct cells select one and look in the top left corner of the spreadsheet to get the address and adjust accordingly. As an aside there are no circumstances under which I would merge cells. Apart from an attempt to make a sheet look pretty I know of no patctical reason for doing so and if you do it then it will eventually bite you on the leg. If you want pretty things then use Powerpoint. Mike "Leslie" wrote: I took your advise and repeated the steps. I carefully pasted in the formula and "Nothing Selected" does display when there is no "x" in cells A1:A7, but nothing appears in the C1 cell when I type an "x" in one of the A1:A7 cells. C1 changes to completely blank. I wonder if this fact is worth mentioning: the cells in column A and B are actually merged cells, for example A1 is merged cells A1:D1, and B1 cell is merged cells E1:H1. Would this change the composition of the formula? Thanks for your patience, Mike. Would it be helpful for you to see a copy of my form firsthand? "Mike H" wrote: Most odd, it works perfectly for me are you sure you pasted the formula exacly as written? You should get 'Nothing selected' if you don't have an x in A1 to A7 What appears in C1 when you put an x in one of the A cells? Mike "Leslie" wrote: Mike - Thank you for responding. I have entered the formula you recommend. Using your formula, I am able to get "Nothing Selected" to display, but I'm not able to get a numerical value from B1:B7 to display in cell C1 "Mike H" wrote: The formula would be helpful <g =IF(ISNA(VLOOKUP("x",A1:B7,2,FALSE)),"Nothing Selected",VLOOKUP("x",A1:B7,2,FALSE)) "Mike H" wrote: To get rid of the error with no x in column A Mike "Leslie" wrote: In one section of my form there are 2 columns: A and B. Cell range B1:B7 contains a series of different numerical values associated with the corresponding cells in cell range A1:A7. Form users will choose the value they want from column B by typing an "x" in the corresponding cell in column A. For example: from the 7 numerical values in column B, the form user may select the value in cell B4 by typing an "x" in cell A4. The value from B4 needs to display in cell C1. So, I need your help to write a formula that tells cell C1 to look at cell range A1:A7 and if any cell in that range contains an "x", to display the contents of the corresponding cell (from cell range B1:B7) in cell C1. Would someone please help me write a formula to accomplish this? Please and Thank you for your assistance! Leslie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|