C creditman New Member Joined Dec 9, 2005 Messages 15 Dec 9, 2005 #1 How do I reference a cell with the column fixed and the row an output of a function? Thanks!
Zack Barresse MrExcel MVP Joined Dec 9, 2003 Messages 10,881 Office Version 3652010 Platform WindowsMobileWeb Dec 9, 2005 #2 Hi, welcome to the board! Is this formula or VBA? Can you describe to us what it is you are trying to do, and what you have to work with?
Hi, welcome to the board! Is this formula or VBA? Can you describe to us what it is you are trying to do, and what you have to work with?
C creditman New Member Joined Dec 9, 2005 Messages 15 Dec 9, 2005 #3 I am working with formulas. I want to subtract the value of a cell who's location is Column: E Row: ROW(MAX(D2:D100))
I am working with formulas. I want to subtract the value of a cell who's location is Column: E Row: ROW(MAX(D2:D100))
P PaddyD MrExcel MVP Joined May 1, 2002 Messages 14,234 Dec 9, 2005 #4 review the excel help file entries for index(), match(), indirect() and offest(). post back if you can't get it sorted.
review the excel help file entries for index(), match(), indirect() and offest(). post back if you can't get it sorted.
C creditman New Member Joined Dec 9, 2005 Messages 15 Dec 11, 2005 #5 ADVERTISEMENT Thank you, but I found another problem. I want the output on MAX($D$2:$D$100) to be the cell that the maximum value is in. How do I do this? Thank you in advance!
ADVERTISEMENT Thank you, but I found another problem. I want the output on MAX($D$2:$D$100) to be the cell that the maximum value is in. How do I do this? Thank you in advance!
barry houdini MrExcel MVP Joined Mar 23, 2005 Messages 20,825 Dec 11, 2005 #6 creditman said: I am working with formulas. I want to subtract the value of a cell who's location is Column: E Row: ROW(MAX(D2:D100)) Click to expand... =LOOKUP(2,1/(D2:D100=MAX(D2:D100)),E2:E100) Note: if there is a tie for MAX(D2:D100) the above formula will give the value in column E corresponding to the last match in D
creditman said: I am working with formulas. I want to subtract the value of a cell who's location is Column: E Row: ROW(MAX(D2:D100)) Click to expand... =LOOKUP(2,1/(D2:D100=MAX(D2:D100)),E2:E100) Note: if there is a tie for MAX(D2:D100) the above formula will give the value in column E corresponding to the last match in D
C creditman New Member Joined Dec 9, 2005 Messages 15 Dec 11, 2005 #7 Thank you all, I fixed the problem with the MATCH and INDEX functions
barry houdini MrExcel MVP Joined Mar 23, 2005 Messages 20,825 Dec 11, 2005 #8 Yeah, I was probably making it too complex above, couldn't you simply use VLOOKUP? =VLOOKUP(MAX(D2:D100),D2:E100,2,0)
Yeah, I was probably making it too complex above, couldn't you simply use VLOOKUP? =VLOOKUP(MAX(D2:D100),D2:E100,2,0)