Consecutive Excel Formulas...?

May 2021 3 61 Report

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.

Computers & Internet / Software

Greg G

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:

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

Anonymous

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