Is there a way to reference the value in a cell as part of a formula, like SUMIF?
Example:
Sumif in 1st worksheet in cell B5 sums colum A in 2nd worksheet (..,...,A1:A50).
On 1st worksheet, I'd like to enter "B" (or a range name) in cell A1.
The SUMIF formula in cell B5 referencing the "B" in cell A1 on the 1st worksheet changes the SUMIF formula to (...,...,B1:B50)
Any help would be greatly appreciated!
If you use letters to identify the column then realistically you're looking at using INDIRECT which will make your SUMIFs Volatile
(there are workarounds to the INDIRECT requirement but they're cumbersome)
Far better to store the column integer (ie 1 for A, 27 for AA etc..) as opposed to the letter, at which point you can use INDEX to create the sum_range in the SUMIF
=SUMIF(...,...,INDEX(Sheet2!$1:$50,0,$A$1))
this will sum rows 1 to 50 in the column identified in A1
(to reiterate, where A1 is the column number rather than letter)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks