Indirect phrase and dragging formula

[AllDay1]

Can some one please help me figure out the proper formula I should be using.

Currently im using =COUNT(INDIRECT("K4:N4"))

This tells me how many of the boxes have a value in them in the four cells K4:N4... Im wanting to use indirect because as the value in say K4 expire, my users would then highlight L4:M4 and drag them left to the cell in column K. Without using indirect, the formula will change my reference field from K4:N4.....

So the problem comes in when I want to drag this formula down and have the K4:N4 update to K5:N5 and so forth.

Im also having the issue with this formula as well. Wanting both K3's to update to K4 and so forth.
=IF(ISBLANK(INDIRECT("K3")),"",(INDIRECT("K3")+365))


Im thinking i should use Row but cannot figure out how to implement.

 

[jasonb75]

Which cell is the formula that looks at K4:N4 going into?

This is easiest done with R1C1 notation which uses offsets based on the position of the formula cell relative to the range used in INDIRECT. If you can tell me which cell the first formula is going into then I can write that one for you as an example rather than trying to explain it in theory.

 

[AllDay1]

Cell C4 for the first line of code i placed.

Cell D4 for the first line of code i placed.

Im adding a picture of concept spreadsheet. in the picture I am on line 3.

 

[AllDay1]

Clarifying so picture and code align.
Cell C3 contains the first line of code =COUNT(INDIRECT("K3:N3"))
Cell D3 contains the second** line of code that i listed. =IF(ISBLANK(INDIRECT("K3")),"",(INDIRECT("K3")+365))


The blue lettering is the only items i want to update as i drag the formulas down.

 

[jasonb75]

As the formula is going in the same row, it's reasonably simple.

=COUNT(INDIRECT("RC11:RC14",0))

=IF(ISBLANK(INDIRECT("RC11",0)),"",(INDIRECT("RC11",0)+365))

In these examples, R with no number means that it looks at the same row as the formula. C11 refers to column 11 (column K) with column a being referred to as C1 (it really is that simple).
Note that you will always need to add ,0 to the formula after the double quotes are closed. This is so that the INDIRECT function knows you are using R1C1 references instead of standard A1 references.

If you need to keep it relative but with the formula in a different row to the data (or column if dragging across) then you have to start using offsets which is the part that is not quite so simple to begin with. You don't need it here but I'll provide a couple of examples in case you need it elsewhere.

In this example, entering the formula into C3 would return a count of the cells K2:N3 with [-1] telling it to start in the row above the formula and [1] telling it to end in the row below.
INDIRECT("R[-1]C11:R[1]C14",0)
When using this method, negative numbers offset up (rows) or left (columns) whilst positive numbers offset down (rows) or right (columns).

Hopefully that all makes sense. I'm logging off for the day now but please let me know if you need me to clarify anything and I'll check in the morning.

 

[AllDay1]

BEYOND HELPFULL, Thank you so much!!!

I learned a lot here. Was unaware that you could use R1C1 in your formula without turning this on for the entire spreadsheet through file-options-formula R1C1 reference style.

I may be pushing it, but is it possible to incorporate ISBLANK FUNCTION into this formula =COUNT(INDIRECT("RC11:RC14",0))

I tried a few time but even when i was able to get excel to accept the formula it still returned 0's when its cells where blank.

Also my name is Jason, so thank you very much again for all your help Jason.

 

[AllDay1]

=COUNT(IF(ISBLANK(INDIRECT("RC15:RC24",0)),"",(INDIRECT("RC15:RC24",0))))

My above live is my attempt to include the blank if 0 function to this line =COUNT(INDIRECT("RC11:RC14",0))

 

[jasonb75]

If you want the result of the formula to be blank instead of 0 then you need to put IF first rather than in the middle.

The traditional way

=IF(COUNT(INDIRECT("RC11:RC14",0))=0,"",COUNT(INDIRECT("RC11:RC14",0)))

The short way making use of newer functions (needs Office 2021 or newer).

=LET(c,COUNT(INDIRECT("RC11:RC14",0)),IF(c,c,""))