Adjustable formulas

Put the rows (values) in their own cell, and then reference them from within the indirect

Take a look at the attached and see if you can adjust the references to suite your needs? I used a different approach

You can do that with the sample I provided...

=COUNTIF(OFFSET($H$1,$C$2-1,0,D2-C2+1,1),">=8")
=COUNTIF(OFFSET($H$1,$C$2-1,0,D2-C2+1,1),8)
=MAX(OFFSET($H$1,$C$2-1,0,D2-C2+1,1))

If you change the values in C2:D2, then the formulas adjust accordingly

If you really want to use indirect()...

=MAX(INDIRECT("H"&$C$2&":H"&$D$2))
=COUNTIF(INDIRECT("H"&$C$2&":H"&$D$2),">=8")
=COUNTIF(INDIRECT("H"&$C$2&":H"&$D$2),8)

I have no idea why you felt the need to give me a negative rep for my efforts here, nor do I undersatand the meaning of your comment...

Pain in the *** Doesn't follow Basic request makes it hard for Requestee some i assume very novice, Just trying to help : ) cheers

If you look at my thread-count (15 000+) and current rep (a few short of 3 000), I can assure you that I am no novice - plus, this forum does not promote novices to Moderator level.

I added to my OFFSET formulas to show they worked in all of your questions...and then I ALSO gave you the INDIRECT() version that you requested, so I really dont see what your problem with my suggestions were?

also...

Oh your File, A8 comes up #REF

That was your own formula I inserted there to see what the problem was.

Anyway...
Based on your last post it seems that yourquestion has been resolved, but you haven't marked your thread as SOLVED. Please use the thread tools towards the top of page to do so

If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

I am not going to get into argument with you over this, but this was what you posted in teh comments when you neg. repped me, it is on record for anyone with the necessary access to see, so it is hardly "abusive and insulting and a blatant lie".

Pain in the *** Doesn't follow Basic request makes it hard for Requestee some i assume very novice, Just trying to help : ) cheers

and the "novice" comment is in the bolded part of your comment. Just for future reference, all comments made when giving reps, are viewable

Often, members ask for help in a particular direction, without being aware that other options are available to them. Most senior members point out those other options as part of a learning excersize, but if the initial direction is insisted on, they try and give that too - thats what I did.

Thank you for your feedback, and I hope you enjoy your continued participation with the forum

i suggest you remove ms office and send it back to Microsoft on the grounds to do not understand any thing suggested and you have no idea what it can /cannot do. then download open office which is poorly supported good luck! and btw negative rep me as much as you like
FDibbins delete this if you feel it's inappropriate

ooh i've been told off

Abusive and Threatening to Participants on this Forum Obviously has not read through this Thread, I have taken a Screen Shot of this Message

OK you have now twice accused me of making false claims - see the attached for your - plus a few other's - comments. These are not something I, or anyone else can make up, so take a look at the file please and then repeat your claim.

@ Martin, I feel your comments may be spot-on here, thanks

Originally Posted by FDibbins

all comments made when giving reps, are viewable

I guess I better watch what I say, then!

Is anything truly private in this forum? How about private messages? I suppose you guys can read those also?

I know that as a mod, I cannot read other people's PM's, nor would i want to

I have PM's disabled for the general membership but mods and admins can still PM me.

OTOH, I've been known to call some folks idiots in the rep comments!

Originally Posted by keen2xl

The Rep request ask "Do i Approve" Or "Disapprove" well i do not Approve of your approach that is why i put "No" it was a "Pain" as in Pain Full experience, which i could have put on this page, but i did that discretely, so you may learn, remember not every one on here is a Wiz, we simply ask basic stuff to be helped with, Not this that or the other.

As a disinterested observer I would like to make a couple of comments.

First, keen2xl, negative rep is not taken lightly on this forum. It should not be given simply because a particular solution does not happen to suit you. Negative rep should be given only when a member is hampering progress more than helping it. That is clearly not the case here, and negative rep is not appropriate. All people offering help here are doing it out of a desire to help people and build a community, and doing it for free I might add. If a particular answer doesn't seem to quite fit what you want, simply move on and hope for a better one. If you really feel you must give feedback of that nature, a private message is better.

Second, I interpret the reference "makes it hard for Requestee some i assume very novice" to mean "your offered help is difficult for other members to understand, some who I assume are novices." It was not intended to call FDibbins a novice. However, I have no comment on whether that statement is actually true.

I see the thread is marked Solved. If the discourse does not return to the problem at hand then the thread will be closed.

I think there is a slight confusion here.

Keen2xl thinks that the rep system is to mark whether the solution has helped him or not and whether he approves of it or not. Well, its not the case, it is whether you approve of giving rep to the user or not. This is not similar to the option that some Q&A sites give you - Has this question been helpful? Yes / No.

This is entirely possible arlu, and 6 may have read the comment in a better light than I did, too

Hi Forum

I am accepting I put 3 Astrix as in the Screen Shot supplied by FDibbins, accepting it as true and correct, for that I sincerely apologise to FDibbins.

For the life of me I don't remember doing those 3 Astrix, but accepting I did, I can see how immediately they could be taken as offensive.
All I can say is they would not have meant to have been, just a simple term people would be familiar with, that's all "Pain in the ***"

But again I apologise.

I appreciate the help all Admin supply on this site and all above comments have been acknowledged.


Couple of points though if I may.

6StringJazzer mentions:"Negative rep should be given only when a member is hampering progress more than helping it".

I'm afraid at the time I felt, sorry to say FDibbins was hampering progress more than helping.


1/ I made a fairly simple request, 3 formulas to link too 2 cells

FDibbins Suggests I do it : It did not help with the answer to my simple question

2/ I have to reply, "I don't how, that's why I’m skiing for help".

FDibbins = Makes non applicable suggestion on a WB also with error in it : It was not the answer to my simple question neither did it help.

3/ I have to reply, explain again, same first request in an even more basic layout, to a fairly simple question .


These are Formulas to adjust

=COUNTIF(INDIRECT("BB23:BB"&$T$16),">0")

=COUNTIF($BQ$23:$BQ$166,"1")

=MAX($BL$23:$BL$166)


FDibbins: After pushing his suggestion again then says, but:

If you really want to use indirect()... "


(Like I’m an idiot if I do)

=MAX(INDIRECT("H"&$C$2&":H"&$D$2))
=COUNTIF(INDIRECT("H"&$C$2&":H"&$D$2),">=8")
=COUNTIF(INDIRECT("H"&$C$2&":H"&$D$2),8)

Above is "FDibbins" best attempt at helping me with the answer after 3 attemps.

What I am trying to get across is, what if I was someone who is even less capable than I with formulas, the above formula answers, look as though they are written in Greek, a nightmare still to sort through.

All I requested help with was an answer to (For a skilled practioner) my fairly simple question, like most other Admin helpers give.

I hope this makes sense to you from this side of the fence and I hope this is taken as constructive input.

Maybe mull it over for a while ?

Regards to all you men.

Especially, FDibbins

Sincerely Mike