Cell Row - Last Cell Entry Reference

  • Thread starter Thread starter auntj
  • Start date Start date
A

auntj

I have tried to figure this out by reading some manuals, but just can't
Perhaps someone here knows the answer. I am doing a weekly report.
have a row of cells and at the end of the Row is a summary section.
need to have the last filled in cell to show in that summary area.
Example:
Line 13 : Cell C - 3 is entered for week 1

Cell F - 7 is entered for week 2

Cell H - 5 is entered for week 3

Summary Cell is T on the same line - for week 1- I want it to show
and for week 2 - I want it to show 7, etc.

Any ideas on how this can be done without doing it maually every week?

Thanks for any suggestions
 
-----Original Message-----
I have tried to figure this out by reading some manuals, but just can't.
Perhaps someone here knows the answer. I am doing a weekly report. I
have a row of cells and at the end of the Row is a summary section. I
need to have the last filled in cell to show in that summary area.
Example:
Line 13 : Cell C - 3 is entered for week 1

Cell F - 7 is entered for week 2

Cell H - 5 is entered for week 3

Summary Cell is T on the same line - for week 1- I want it to show 3
and for week 2 - I want it to show 7, etc.

Any ideas on how this can be done without doing it maually every week?
Click to expand...

This formula takes the last date first you will have to
expand it to include wks 4 and 5 and also the line
numbers. But if you have space in line 4 paste the formula
in then copy to T13

=IF(H4>0,H4,IF(F4>0,F4,IF(C4>0,C4)))

Add for weeks 4 and 5 at the start of the formula.

Regards
Peter
 
Try this formula

=INDEX(C3:S3,1,MAX(IF(C3:S3<>"",COLUMN(C3:S3)))-COLUMN(C3:S3)+1)

it's an array formula, so enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob-
Thanks for the formula. It worked, but when I went to use it i
the next row - it didn't. I changed reference numbers, but had n
luck. I need to use this formula on about 20 various rows on m
worksheet. Sorry that I neglected to mention that in my original post
just thought that if I got a working formula all I had to do was chang
the line #.
Example:
Need to use this formula on Lines 13, 14, 15 and the
again on Lines 20,21,22 and again on Lines 27,28,29 - etc.
Thanks for your help
 
I have just tried it again, and it works fine for me.

You are correct that all you need top do is change the line. This is what
the line 13, 14 formulae look like on my system which is working
{=INDEX(C13:S13,1,MAX(IF(C13:S13<>"",COLUMN(C13:S13)))-COLUMN(C13:S13)+1)}
{=INDEX(C14:S14,1,MAX(IF(C14:S14<>"",COLUMN(C14:S14)))-COLUMN(C14:S14)+1)}
the curly brackets are put in by Excel when you Ctrl-Shift-Enter the array
formula.

You don't need to enter the formula multiple times, just copy the first one
entered, say T13, and then paste into T13, T14, etc, Excel will update the
row numbers.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I'm so glad I was able to find this Forum. You have all been s
helpful. The formulas work and now tomorrow I'll be able to finish u
my report with ease.
Many, many thanks
 
Back
Top