Home |
Search |
Today's Posts |
#41
|
|||
|
|||
I would like to send the sheet to you however all I have is a list of names
and dollar amounts. This represent earnings that I am tracking. There is the additional letter as a trailer to some of the names.(this designates participation in an additional group. The use of clean function does deletethe letter but the hidden space remains. The use of the trim function can not correct this situation. I copy the list from the web. If you can suggest how I might send it to you I will be pleasedto do so. Or any other suggestions you might have -- George "Ragdyer" wrote: If you wish, you can send me your sheet, and I'll see what I can figure out. Cut out cutout from my address. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case one received 32 as result. when I then go to search replace I have entered spaces to search and spaces to replace( four blank spaces to search and two blank spaces to replace.)This does not work and can't figure how else to use the search replace. Any further help appreciated -- George "RagDyer" wrote: I would guess that perhaps the data that's in AX3 doesn't *exactly* match what's in Column B. Do you import any of your data? Are the names "full" names, first, and/or middle and last names, where there might be a possibilty that the spaces between them might not be a normal Char(32) space? Could there be a possibility of leading and/or trailing spaces? For a test, key a name into Column B. Enter that same name in *exactly* the same way into AX3, and see if you get a correct return from your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I tried this formula and get a "not found" also this is how the formula gets entered. Don't know why. It was entered as below. =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18 ,0))) "RagDyeR" wrote: Try this in Ay3 of "Miriam": =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0))) You can then *double click* on the "fill handle" in the lower right corner of AY3, which will *automatically* copy the formula in AY3 down Column AY, as far as there is data in Column AX. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I am confronted with a new scenario which I'll present for help. One worksheet titled geo I have a list of names b4 thru b18 and data in k4thru k18. I want to transfere the data to worksheet titled miriam after searching for the exact names. The names of the second worksheet are column ax3 thru ax89. Hope this makes sense to you. George "RagDyer" wrote: First question: Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. :) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#42
|
|||
|
|||
A new question.
I am able to copy data from column of numbers to a corresponding column using the following basic formula =b11 to represent column b row 11. In the corresponding column I can drag the first cell data down the column and all data is transfered. I there a formula to use to data from rows to columns using the same concept. I am looking to have the column heading change and the data row remaing the same. -- George "George A. Yorks" wrote: I would like to send the sheet to you however all I have is a list of names and dollar amounts. This represent earnings that I am tracking. There is the additional letter as a trailer to some of the names.(this designates participation in an additional group. The use of clean function does deletethe letter but the hidden space remains. The use of the trim function can not correct this situation. I copy the list from the web. If you can suggest how I might send it to you I will be pleasedto do so. Or any other suggestions you might have -- George "Ragdyer" wrote: If you wish, you can send me your sheet, and I'll see what I can figure out. Cut out cutout from my address. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case one received 32 as result. when I then go to search replace I have entered spaces to search and spaces to replace( four blank spaces to search and two blank spaces to replace.)This does not work and can't figure how else to use the search replace. Any further help appreciated -- George "RagDyer" wrote: I would guess that perhaps the data that's in AX3 doesn't *exactly* match what's in Column B. Do you import any of your data? Are the names "full" names, first, and/or middle and last names, where there might be a possibilty that the spaces between them might not be a normal Char(32) space? Could there be a possibility of leading and/or trailing spaces? For a test, key a name into Column B. Enter that same name in *exactly* the same way into AX3, and see if you get a correct return from your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I tried this formula and get a "not found" also this is how the formula gets entered. Don't know why. It was entered as below. =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18 ,0))) "RagDyeR" wrote: Try this in Ay3 of "Miriam": =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0))) You can then *double click* on the "fill handle" in the lower right corner of AY3, which will *automatically* copy the formula in AY3 down Column AY, as far as there is data in Column AX. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I am confronted with a new scenario which I'll present for help. One worksheet titled geo I have a list of names b4 thru b18 and data in k4thru k18. I want to transfere the data to worksheet titled miriam after searching for the exact names. The names of the second worksheet are column ax3 thru ax89. Hope this makes sense to you. George "RagDyer" wrote: First question: Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. :) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#43
|
|||
|
|||
This will copy Row 10, from Column A, to as many columns as you drag down
the formula: .. =INDEX($10:$10,,ROW(A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... A new question. I am able to copy data from column of numbers to a corresponding column using the following basic formula =b11 to represent column b row 11. In the corresponding column I can drag the first cell data down the column and all data is transfered. I there a formula to use to data from rows to columns using the same concept. I am looking to have the column heading change and the data row remaing the same. -- George "George A. Yorks" wrote: I would like to send the sheet to you however all I have is a list of names and dollar amounts. This represent earnings that I am tracking. There is the additional letter as a trailer to some of the names.(this designates participation in an additional group. The use of clean function does deletethe letter but the hidden space remains. The use of the trim function can not correct this situation. I copy the list from the web. If you can suggest how I might send it to you I will be pleasedto do so. Or any other suggestions you might have -- George "Ragdyer" wrote: If you wish, you can send me your sheet, and I'll see what I can figure out. Cut out cutout from my address. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "George A. Yorks" .(donotspam) wrote in message ... I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case one received 32 as result. when I then go to search replace I have entered spaces to search and spaces to replace( four blank spaces to search and two blank spaces to replace.)This does not work and can't figure how else to use the search replace. Any further help appreciated -- George "RagDyer" wrote: I would guess that perhaps the data that's in AX3 doesn't *exactly* match what's in Column B. Do you import any of your data? Are the names "full" names, first, and/or middle and last names, where there might be a possibilty that the spaces between them might not be a normal Char(32) space? Could there be a possibility of leading and/or trailing spaces? For a test, key a name into Column B. Enter that same name in *exactly* the same way into AX3, and see if you get a correct return from your formula. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... I tried this formula and get a "not found" also this is how the formula gets entered. Don't know why. It was entered as below. =IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18 ,0))) "RagDyeR" wrote: Try this in Ay3 of "Miriam": =IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0))) You can then *double click* on the "fill handle" in the lower right corner of AY3, which will *automatically* copy the formula in AY3 down Column AY, as far as there is data in Column AX. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I am confronted with a new scenario which I'll present for help. One worksheet titled geo I have a list of names b4 thru b18 and data in k4thru k18. I want to transfere the data to worksheet titled miriam after searching for the exact names. The names of the second worksheet are column ax3 thru ax89. Hope this makes sense to you. George "RagDyer" wrote: First question: Replace error message with a null ( "" ), which can then be added: =IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "George A. Yorks" .(donotspam) wrote in message ... I keep saying thanks, your help is outstanding. I would like to ask two additional questions. ie: When I enter my formula into the cells of column A in a number of cells it returns a value in a few it returns#N/A. There is no data in those cases but with the #n?A when I try to add the columns will not do so as it cant enter a non digit. I'm using 0 for the last number in my formula. How best to get around this. Also I've copied a table from USA internet. In pasting to worksheet all the cells are obliterated and nothing is recognized by excel. The data is however recognized in (pardon me) lotus. Is there anyway of having my vlookup formula search 123 in place of a sheet in excel. =vlookup(a1, sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone "Earl Kiosterud" wrote: George, The 4 is the third parameter of the VLOOKUP function, and tells it to retrieve the cell in the 4th column of the range being looked up in. Did I say being looked up in? Oh, well. :) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. I know I'll fully understand the formula structure soon. One question, at end of formula ,4,0 what in fact does the 4 make reference to? "Ragdyer" wrote: Try this: =VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0) -- HTH, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "George A. Yorks" .(donotspam) wrote in message ... Thanks for the help. One further question. In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns. If there is a column between so there is data in a and c and none in b the result return err,the formula in this cell referes to cells that are currently emply. How to get around this. ie" if sheet two has data in column a and column d. thanks George Yorks "Earl Kiosterud" wrote: George, In B1 of sheet 1: =VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE) Copy down with fill handle to B10. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "George A. Yorks" .(donotspam) wrote in message ... Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
#44
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating a formul
I have two questions that hope I can get help with.
have lotus worksheet that is read only. I followed instructions from help menu but get an error message: an error occured applying atributes to the file, access denied. The file is on a CD, selected it, went to file,properties and removed the check for read only. Then the error message. Second question, Have a worksheet with 50 rows. have a second worksheet with 50 calculations. Want to create a formula that will transfere numbers at 29 number intervals to each of the 50 rows any and all help appreciated -- George "George A. Yorks" wrote: Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating custom list with a comma in it | Excel Discussion (Misc queries) | |||
Eliminate creating list that returns blank cells | Excel Worksheet Functions | |||
Creating a Microsoft Words document from an existing Excel spreads | New Users to Excel | |||
creating an x,y chart | Charts and Charting in Excel | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions |