| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell referncing
Is there a way to refernce the cell name, ie C3 instead of the contents in
it, using formulas in another cell |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Look in HELP for the INDIRECT() function
-- Kind regards, Niek Otten Microsoft MVP - Excel "heather" wrote in message ... Is there a way to refernce the cell name, ie C3 instead of the contents in it, using formulas in another cell |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
ok, so I have multiple numbers in column A, some repeat, and corresponding
numbers in column B. I want to pull all the the corresponding numbers in Column B by the number in Column A but since some of the numbers in Column A repeat I get the number for the first number not the repeated number....What can I do? "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "heather" wrote in message ... Is there a way to refernce the cell name, ie C3 instead of the contents in it, using formulas in another cell |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
I fail to see what this has to do with your initial question.
Please describe more clearly what you try to achieve. Give examples of data and required results if possible. -- Kind regards, Niek Otten Microsoft MVP - Excel "heather" wrote in message ... ok, so I have multiple numbers in column A, some repeat, and corresponding numbers in column B. I want to pull all the the corresponding numbers in Column B by the number in Column A but since some of the numbers in Column A repeat I get the number for the first number not the repeated number....What can I do? "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "heather" wrote in message ... Is there a way to refernce the cell name, ie C3 instead of the contents in it, using formulas in another cell |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Well, it doesn't really have anything to do with it. The first question
didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. "Niek Otten" wrote: I fail to see what this has to do with your initial question. Please describe more clearly what you try to achieve. Give examples of data and required results if possible. -- Kind regards, Niek Otten Microsoft MVP - Excel "heather" wrote in message ... ok, so I have multiple numbers in column A, some repeat, and corresponding numbers in column B. I want to pull all the the corresponding numbers in Column B by the number in Column A but since some of the numbers in Column A repeat I get the number for the first number not the repeated number....What can I do? "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "heather" wrote in message ... Is there a way to refernce the cell name, ie C3 instead of the contents in it, using formulas in another cell |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
"Glenn" wrote: heather wrote: Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. Ok, I tried that formula but for the numbers in column A that duplicate all it returned to me is #num! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
"Glenn" wrote: heather wrote: Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. Ok, I tried that formula but for the numbers in column A that duplicate all it returned to me is #num! With the data above it worked fine. Can you put a copy of your worksheet on www.savefile.com for someone to look at? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
i put it on there....this is the link it gave
me....http://savefile.com/files/1978951 Thanks for the help "Glenn" wrote: heather wrote: "Glenn" wrote: heather wrote: Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. Ok, I tried that formula but for the numbers in column A that duplicate all it returned to me is #num! With the data above it worked fine. Can you put a copy of your worksheet on www.savefile.com for someone to look at? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
i put it on there....this is the link it gave me....http://savefile.com/files/1978951 Thanks for the help "Glenn" wrote: heather wrote: "Glenn" wrote: heather wrote: Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. Ok, I tried that formula but for the numbers in column A that duplicate all it returned to me is #num! With the data above it worked fine. Can you put a copy of your worksheet on www.savefile.com for someone to look at? You said columns C and D, but you really wanted columns H and I. H2 = LARGE($A$2:$A$1000,ROW()-1) I2 = IF(H2=H1,INDEX(INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(H2,INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(H2,$A$2:$A$1000,0))) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Glenn wrote:
heather wrote: i put it on there....this is the link it gave me....http://savefile.com/files/1978951 Thanks for the help http://www.savefile.com/files/1978966 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
"Glenn" wrote: heather wrote: i put it on there....this is the link it gave me....http://savefile.com/files/1978951 Thanks for the help "Glenn" wrote: heather wrote: "Glenn" wrote: heather wrote: Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. Ok, I tried that formula but for the numbers in column A that duplicate all it returned to me is #num! With the data above it worked fine. Can you put a copy of your worksheet on www.savefile.com for someone to look at? You said columns C and D, but you really wanted columns H and I. H2 = LARGE($A$2:$A$1000,ROW()-1) I2 = IF(H2=H1,INDEX(INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(H2,INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(H2,$A$2:$A$1000,0))) AWESOME!!!!!!!! You are the BEST!~!!! Thank you soooooooo much!!!!!!! 18,000 gold stars for you! |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Glenn, or whoever, after the problem below I am now finding it difficult to
sum the $ in col R by the number in Col I....example in http://www.savefile.com/projects/808732916 I would like it to look like COL S in the end...How can I do this "heather" wrote: "Glenn" wrote: heather wrote: i put it on there....this is the link it gave me....http://savefile.com/files/1978951 Thanks for the help "Glenn" wrote: heather wrote: "Glenn" wrote: heather wrote: Well, it doesn't really have anything to do with it. The first question didn't work so I am going about it from a different angle....Here is what i would like to happen.... Col A Col B Col C col D 699875 0102802NTZG 699875 0102802NTZG 345666 0102802NUCU 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA 699875 0107802OBEX 548986 0104802NWPZ 699875 0107802OBFA etc... 462083 0107802OBZY 472550 0107802OCDY Column C and D I would like to get some formula to auto enter the info. Number from largest to smallest which i used the Larger() ...and Column D to pull the corresponding number/letter seq from column B. One possible way... C2 = LARGE($A$2:$A$1000,ROW()-1) D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(C2,INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(C2,$A$2:$A$1000,0))) Adjust the "1000"'s to include all of your data, then copy both down as far as needed. Ok, I tried that formula but for the numbers in column A that duplicate all it returned to me is #num! With the data above it worked fine. Can you put a copy of your worksheet on www.savefile.com for someone to look at? You said columns C and D, but you really wanted columns H and I. H2 = LARGE($A$2:$A$1000,ROW()-1) I2 = IF(H2=H1,INDEX(INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1 000,0)+2,2)&":B1000"), MATCH(H2,INDIRECT(ADDRESS(MATCH(I1,$B$2:$B$1000,0) +2,1)&":A1000"),0)), INDEX($B$2:$B$1000,MATCH(H2,$A$2:$A$1000,0))) AWESOME!!!!!!!! You are the BEST!~!!! Thank you soooooooo much!!!!!!! 18,000 gold stars for you! |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
Glenn, or whoever, after the problem below I am now finding it difficult to sum the $ in col R by the number in Col I....example in http://www.savefile.com/projects/808732916 I would like it to look like COL S in the end...How can I do this http://www.savefile.com/files/1982535 I modified several of your formulas and added what you need in R. Post back if there are any problems. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
"Glenn" wrote: heather wrote: Glenn, or whoever, after the problem below I am now finding it difficult to sum the $ in col R by the number in Col I....example in http://www.savefile.com/projects/808732916 I would like it to look like COL S in the end...How can I do this http://www.savefile.com/files/1982535 I modified several of your formulas and added what you need in R. Post back if there are any problems. No problems, but thanks so much for getting that for me and editing all those other col. I wasn't sure what they were but they came up with the right answer but thats for making it easier!!!! You have been a GREAT help! Thanks |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
"Glenn" wrote: heather wrote: Glenn, or whoever, after the problem below I am now finding it difficult to sum the $ in col R by the number in Col I....example in http://www.savefile.com/projects/808732916 I would like it to look like COL S in the end...How can I do this http://www.savefile.com/files/1982535 I modified several of your formulas and added what you need in R. Post back if there are any problems. No problems, but thanks so much for getting that for me and editing all those other col. I wasn't sure what they were but they came up with the right answer but thats for making it easier!!!! You have been a GREAT help! Thanks Do your best to figure out how each of the modified formulas works. Use sources like the help file and web sites like http://www.contextures.com/tiptech.html for information. Once you understand how they work, you can make sure that they continue to provide the correct answer in the future, in case your data changes in ways that weren't obvious to me at this time. |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Tried to use the sameformula from earlier in a different file modifying the
col and what not and it did not work...Not sure why but eveything was the same...help?! http://www.savefile.com/projects/808732916 all i did was copy and paste and then edited the info to make sense in the new file...for some reason i cant get it to work... I used the evaluate formula to see what went wrong and i cant figure it out,.... please help "Glenn" wrote: heather wrote: Glenn, or whoever, after the problem below I am now finding it difficult to sum the $ in col R by the number in Col I....example in http://www.savefile.com/projects/808732916 I would like it to look like COL S in the end...How can I do this http://www.savefile.com/files/1982535 I modified several of your formulas and added what you need in R. Post back if there are any problems. |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
Tried to use the sameformula from earlier in a different file modifying the col and what not and it did not work...Not sure why but eveything was the same...help?! http://www.savefile.com/projects/808732916 all i did was copy and paste and then edited the info to make sense in the new file...for some reason i cant get it to work... I used the evaluate formula to see what went wrong and i cant figure it out,.... please help If all you need is a list of item numbers and the total cost, consider using a PivotTable instead of formulas: http://www.savefile.com/files/1983960 If you're not familiar with PivotTables, you can look he http://www.peltiertech.com/Excel/Pivots/pivottables.htm |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Help! Trying to refernce more cells but this time in different sheets. I have
revamped the formula but i can't figure it out her is sample.. http://www.savefile.com/projects/808732916 "Glenn" wrote: heather wrote: Tried to use the sameformula from earlier in a different file modifying the col and what not and it did not work...Not sure why but eveything was the same...help?! http://www.savefile.com/projects/808732916 all i did was copy and paste and then edited the info to make sense in the new file...for some reason i cant get it to work... I used the evaluate formula to see what went wrong and i cant figure it out,.... please help If all you need is a list of item numbers and the total cost, consider using a PivotTable instead of formulas: http://www.savefile.com/files/1983960 If you're not familiar with PivotTables, you can look he http://www.peltiertech.com/Excel/Pivots/pivottables.htm |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
heather wrote:
Help! Trying to refernce more cells but this time in different sheets. I have revamped the formula but i can't figure it out here is sample.. http://www.savefile.com/projects/808732916 I think this is what you are looking for: =IF(A3=A2,INDEX(INDIRECT("Sheet2!"&ADDRESS(MATCH(E 2,Sheet2!$B$1:$B$40,0)+1,2)&":$B$40"), MATCH(A3,INDIRECT("Sheet2!"&ADDRESS(MATCH(E2,Sheet 2!$B$1:$B$40,0)+1,1)&":$A$40"),0)), INDEX(Sheet2!$B$1:$B$40,MATCH(A3,Sheet2!$A$1:$A$40 ,0))) |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell referncing
Awesome Thank You, again! I didn't know what to put in there to include the
second sheet. I didn't know I could use & for it! Thanks very much once again. "Glenn" wrote: heather wrote: Help! Trying to refernce more cells but this time in different sheets. I have revamped the formula but i can't figure it out here is sample.. http://www.savefile.com/projects/808732916 I think this is what you are looking for: =IF(A3=A2,INDEX(INDIRECT("Sheet2!"&ADDRESS(MATCH(E 2,Sheet2!$B$1:$B$40,0)+1,2)&":$B$40"), MATCH(A3,INDIRECT("Sheet2!"&ADDRESS(MATCH(E2,Sheet 2!$B$1:$B$40,0)+1,1)&":$A$40"),0)), INDEX(Sheet2!$B$1:$B$40,MATCH(A3,Sheet2!$A$1:$A$40 ,0))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referncing Rows of Filtered Data | Excel Worksheet Functions | |||
| Referncing the current cell name in an Excel MACRO | Excel Discussion (Misc queries) | |||
| How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
| Getpivotdata - referncing a data_field resulting in "REF! | Excel Worksheet Functions | |||
| concatenate cell and referncing afterwads | Excel Discussion (Misc queries) | |||
Linear Mode
