Cell referncing

[heather]

Is there a way to refernce the cell name, ie C3 instead of the contents in
it, using formulas in another cell

 

[Niek Otten]

Look in HELP for the INDIRECT() function

 

[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?

 

[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.

 

[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.

 

[Glenn]

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.

 

[heather]

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.

Ok, I tried that formula but for the numbers in column A that duplicate all
it returned to me is #num!

 

[Glenn]

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?

 

[heather]

i put it on there....this is the link it gave
me....http://savefile.com/files/1978951
Thanks for the help

 

[Glenn]

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)))

 

[Glenn]

http://www.savefile.com/files/1978966

 

[heather]

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!

 

[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

 

[Glenn]

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.

 

[heather]

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

 

[Glenn]

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.

 

[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

 

[Glenn]

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

 

[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

 

[Glenn]

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)))