Consecutive Excel Formulas...?
I have a problem with creative consecutive formulas. I currently have a large spreadsheet that needs to be filled with formulas.
The current formula is IF(ISBLANK('FY13'!$K4),"",'FY13'!$K4)
FY13 is the file name and I need to $K4 to fall consecutively in the entire worksheet.
The problem is when I auto fill the formula converts the "4" of the K4 but I need the "K" to transform into the next letters. (for example...K4. L4, M4 rather then K4, K5, K6)
I thought it might help if I wrote K$4 but no luck. I would like to auto fill the sheet with the formulas rather than input the letters individually. I apologize for the lack of vocabulary, I just don't know the excel lingo. Any help will be greatly appreciated. Thank you.
More Questions From This User See All
Answers & Comments
Verified answer
Changing to K$4 will change column letters, but not the row number.
Remove the $ from both, so you just have K4 to start with.
Copying down will change row values from 4, to 5, 6, etc.. and copying across will change column's from K to L, M, N, etc..
You can toggle through the static/dynamic options by selecting the $K4 in your formula and pressing F4.
Results will change as such:
K4
$K$4
K$4
$K4
Also, I'm a bit confused by your formula. If the cell K4 in FY13 is blank, show a blank, otherwise show what's in K4 of FY13. This seems to be redundant. You're showing the value of K4 from FY13 no matter what, so ='FY13'!K4 would be the same thing, no IF statement necessary. If you're planning to put something inside the quotes for the value_if_true, then that's a different matter.
hmmm... IF(500*a million,A1+a million,IF(500*2,A1+2, IF(500*3,A1+3,A1))) …what cellular or cells are the potential consecutive numbers in? 500*3 is 1500 so if A1 has 1500 then in B1 you ought to in basic terms positioned the formula and get 1503 =IF(A1/500=INT(A1/500), A1+(A1/500),A1) sense free to replace
Try this..
=IF(ISBLANK(OFFSET ('FY13'!$K$4,0,ROW(A1)-1)),"",OFFSET ('FY13'!$K$4,0,ROW(A1)-1))