Reference last number in a column and offset

[Flexremmington]

Hi all- I'm sure this has been answered elsewhere, but I guess I just don't know how to correctly search it.

Column A is my input and J is my output. A1:A4 will always be the same. The employee number, in this case A5:A9 will be different with each time I use the sheet. "Total Monthly", currently A14, will always be 5 cells below the last employee number.

I need to put any amount of employees in and then have all employees represented in Col J and the total monthly cell listed directly under the last number.

Hope this makes sense, and thank you!

 

[Sufiyan97]

Do you want something like this?

Book1
	A								I	J	K
1	Plan 1									Plan 1
2	Network									Network
3	Rx Option									Rx Option
4	Employee Number									Employee Number
5	1									1
6	2									2
7	3									3
8	4									4
9	5									5
10	6									6
11	7									7
12										Total Monthly
13
Sheet1

Cell Formulas
Range	Formula
J1:J12	J1	=IF(ISBLANK(A1),"Total Monthly",A1)

 

[Flexremmington]

Do you want something like this?

Almost, but I need it to reference the the actual cell instead of just the text. I have more data in that row that needs to be referenced.

 

[Joe4]

You should be able to extend the same logic that Sufiyan97 gave your for all the other cells.
Exactly which cells in your example above are you trying to populate with a formula?

 

[Flexremmington]

You should be able to extend the same logic that Sufiyan97 gave your for all the other cells.
Exactly which cells in your example above are you trying to populate with a formula?

Cell M10 needs populated by E14, N10 by H14. But if I add five more employees to the list that will change the total monthly row to 19. E14 &H14 are sum formulas.

 

[Joe4]

So totals in cells E14 and H14 may move down rows, but the totals on the other side will ALWAYS be in cells M10 and N10?
Is that correct?

 

[Flexremmington]

So totals in cells E14 and H14 may move down rows, but the totals on the other side will ALWAYS be in cells M10 and N10?
Is that correct?

No, they are all relative. The "Total Monthly" cell will always be 5 cells lower than whatever the last employee number is.

Here's my full sheet without columns hidden.

 

[Joe4]

Try these formulas:

In cell M10:

=INDEX(E:E,MATCH("Total Monthly",A:A,0))

In cell N10:

=INDEX(H:H,MATCH("Total Monthly",A:A,0))