H
heather
Is there a way to refernce the cell name, ie C3 instead of the contents in
it, using formulas in another cell
it, using formulas in another cell
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Niek Otten
Look in HELP for the INDIRECT() function
H
heather
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?
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?
N
Niek Otten
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.
Please describe more clearly what you try to achieve. Give examples of data
and required results if possible.
H
heather
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.
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.
G
Glenn
heather said: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$1000,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.
H
heather
Ok, I tried that formula but for the numbers in column A that duplicate allGlenn said:One possible way...
C2 = LARGE($A$2:$A$1000,ROW()-1)
D2 = IF(C2=C1,INDEX(INDIRECT(ADDRESS(MATCH(D1,$B$2:$B$1000,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.
it returned to me is #num!
G
Glenn
heather said: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?
H
heather
i put it on there....this is the link it gave
me....http://savefile.com/files/1978951
Thanks for the help
me....http://savefile.com/files/1978951
Thanks for the help
G
Glenn
heather said:i put it on there....this is the link it gave
me....http://savefile.com/files/1978951
Thanks for the help
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$1000,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)))
H
heather
Glenn said: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$1000,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!
H
heather
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
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
G
Glenn
heather said: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.
H
heather
No problems, but thanks so much for getting that for me and editing allGlenn said: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.
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
G
Glenn
heather said: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.
H
heather
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
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
G
Glenn
heather said: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 here:
http://www.peltiertech.com/Excel/Pivots/pivottables.htm
H
heather
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
revamped the formula but i can't figure it out her is sample..
http://www.savefile.com/projects/808732916
G
Glenn
heather said: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(E2,Sheet2!$B$1:$B$40,0)+1,2)&":$B$40"),
MATCH(A3,INDIRECT("Sheet2!"&ADDRESS(MATCH(E2,Sheet2!$B$1:$B$40,0)+1,1)&":$A$40"),0)),
INDEX(Sheet2!$B$1:$B$40,MATCH(A3,Sheet2!$A$1:$A$40,0)))