Returning Cell reference

**elad** · 12-10-2007, 02:46 PM

Hi, I have a sheet containing collums with month an weeks. I try to find out which collum the first week start and where te last week the same mont ends.

Let say A5:E5 is filled with december in each cell. The row below (A6:E6) says week 48, week 49, week 50, week 51, week 52.

I want a formula telling me de Cell reference where desember begins en where it ends. In my Excample (A5 and E5). If Possible, I also could prefer that the cell reference was to rows below start/end. In my example (A7 and E7)

Thanks for any help
elad
Norway

**NBVC** · 12-10-2007, 04:02 PM

To get cell address 2 rows below 1st occurance of "december"

Try:

=ADDRESS(7,MATCH(TRUE,(5:5="december"),0))

this formula needs to be confirmed with the CTRL+SHIFT+ENTER keys not just ENtER.

To get cell address 2 rows below last occurance of "december"

Try:

=ADDRESS(7,LOOKUP(2,1/(5:5="december"),6:6))

this formula only needs to be confirmed with ENTER.

**elad** · 12-10-2007, 04:45 PM

Hi and thanks!

The first formula worked fine, but the second did not. I got the #value result.....

Any other sugestions?

**NBVC** · 12-10-2007, 05:00 PM

How about just?

=ADDRESS(7,MATCH("december",5:5))

**elad** · 12-10-2007, 05:10 PM

Brilliant!

Now I have the range, I have to sum all the Yes/no answer between first cell/last cell in that area. I will try to find out how, thinking indirect maybe will help me.

Again - Thanks - saving me a lot of time!

**NBVC** · 12-10-2007, 05:19 PM

How about?

Please Login or Register  to view this content.

for yes's and

Please Login or Register  to view this content.

for no's

both formulas confirmed with CTRL+SHIFT+ENTER

**elad** · 12-11-2007, 09:17 AM

Impressing!

The function worked very good!

The 7:7 statment tells which row it shall search in. Is it possible to search in many rows, say A7 to E100?

**NBVC** · 12-11-2007, 09:27 AM

You know, revisiting your problem from the start... at first, you seemed to just want to know where "december" started and where it ended... then you wanted the sum between these 2 cells.... now, I am thinking you just want to sum the number of yes's and no's where there is "december" in row 5. Is this right?

If so, then a much simpler formula like the following should work for you:

=SUMPRODUCT((A5:E5="december")*(A7:E100="yes"))

and

=SUMPRODUCT((A5:E5="december")*(A7:E100="no"))

**elad** · 12-11-2007, 09:37 AM

Almost, but I "dont know" where december starts and ends. Next year, maybe december has 4 weeks so I have to find the colum for start/end.

So I cant use =sumproduct((A5:E5="december").......... because I dont know If descember starts in A5 and ends in E5.

**NBVC** · 12-11-2007, 09:40 AM

But if you expand the ranges to look at the entire row, then it doesn't matter where it starts or ends, it will look in all cells in the row and find all "december's"

e.g =SUMPRODUCT((A5:IU5="december")*(A7:IU100="yes"))

note: I went to IU instead of IV, because sumproduct doesn't accept references to entire columns/rows (unless you are using 2007).

**elad** · 12-11-2007, 09:55 AM

Sorry, but that does not work. If I try that function it gives me #ref result.

**NBVC** · 12-11-2007, 10:03 AM

Works for me...

Do you have anything else on the sheet between columns E and IU? Are there any other error messages in any cells on the sheet within those ranges....

EDIT:

IF you have to absolutely use your from 1st to Last "december" technique, then try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

**elad** · 12-11-2007, 10:26 AM

Yepp, I had a #REF in the same range. When I deleted this, the function worked very good!

I will try the other sugestion to, but I think the last solution was the best.

Again -thank you very much!

**elad** · 12-11-2007, 02:56 PM

Hi again, I have a simular problem with another sheet, so i post in the same thread. In this sheet I have more multiple criterias. It goes like this:
If month = december (row 11), and Category = 2 (row 14) then sum Time. In uploade example the result should be 8. I have tried with SUMPRODUCT, but I cant figure out the right combination.

**NBVC** · 12-11-2007, 03:08 PM

Try:

Please Login or Register  to view this content.

make sure your range starts in Column B

**elad** · 12-11-2007, 04:58 PM

=SUMPRODUCT((C11:K11="dec")*(C14:K14="category")*(C15:K15=2)*(OFFSET(C15:K15;0;-1)))

I change the formula a bit because my range starts in column C, and for testing ends in Column K. Unfortunatly, it gives me the #VALUE result.

"Dec" start is C11
"Category" start is C14 (first apperance of category is E14)
The range with variable data (values of time and category) f.eks C15:K50

**NBVC** · 12-11-2007, 05:18 PM

works for me....see attached....

you sure that the month is entered just as a text string (i.e. "dec") or is it a date formatted as "mmm" to display the month and that there are no other errors within any of those ranges?

Note: You may have to change my commas to your semi-colons to get it to work on your version of Excel

**elad** · 12-11-2007, 05:47 PM

Yor example worked. And I have found out way it did not worked for me. In cell C15 its a description of time an Category. In my uploaded example the word "test" is written in cell C15. Is there anyway to solve that?

**NBVC** · 12-11-2007, 05:52 PM

Please upload your sample and explain again. I don't understand what you mean.

Make sure it is a zipped XL2003 or earlier version.

**elad** · 12-12-2007, 05:33 AM

I have uploaded the example. The function work if I dont put any text in cell C15 and F15. The text is a description of the value used to tell what kind of activity it is (Chargable time/Non Chargable).

**NBVC** · 12-12-2007, 08:51 AM

I see now...

Try this instead:

Please Login or Register  to view this content.

**elad** · 12-12-2007, 10:42 AM

YEPP!!!

Now it works perfect!!

Thanks a lot -great help and very fast respons!

elad
Norway

Returning Cell reference

LinkBack

Thread Tools

Rate This Thread

Display

Returning Cell reference

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions