Increment columns in a formula while dragging down

[Cowboy95]

Sorry, I have other threads very similar to this, but I can't quite get the solutions found there to work for me.

This is a very simple formula, hoping for a simple solution. Formula: =IF(C7="",NA(),C7).

I would like to copy/drag down a column having the column change, not the row, so =IF(D7="",NA(),D7), =IF(E7="",NA(),E7), and so on.

Thanks in advance!

 

[Fluff]

What cell are you putting that formula in to start with?

 

[Cowboy95]

What cell are you putting that formula in to start with?

The cell the formula is in is T5. I was using a simple cell reference =C7, but I now would like for a return of #NA if C7 has no value.

 

[Fluff]

Ok, how about

=LET(I,INDEX($C$7:$S$7,ROWS(T$5:T5)),IF(I="",NA(),I))

or a spill formula

=LET(I,INDEX($C$7:$S$7,SEQUENCE(17)),IF(I="",NA(),I))

 

[Cowboy95]

It's very possible it has something to do with my implementation of this formula, but those formulas returned #name?.

Also, wouldn't I need to remove the $ for the cell references for it to incrementally change columns?

I can't upload the mini-sheet app at this time, but will add a couple of pictures for reference.

These are the cells being referenced, where the input will occur.

I have manually typed out the formula from my original post to return the #NA, which is what you see here, but I need to do this for many more exams.

 

[Fluff]

Do you have the LET function?

 

[Cowboy95]

Do you have the LET function?

I do, I have no experience in using it.

 

[Fluff]

In that case can you post an image of exactly what & where the formula is.

 

[Cowboy95]

Fluff, thank you for your help on this. I have figured out a simpler solution to accomplish what I need, leave it to me to make things difficult! However, I am surprised that such a complex (at least in my mind) formula is needed to accomplish my original request.

My solution was to switch the months and categories row/columns, so the months were listed in the columns instead of the rows, this allowed to easily drag/copy the formula across the columns, changing the columns in the formula while the row remained the same.

 

[Fluff]

Glad you sorted it & thanks for the feedback.