• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Searching across a range of cells

S

semper5

Member
I am trying to find a work-around for:

Have excel look at a range of 7 cells, and if any of the cells contain a number, compare it, and then have it place that value.

Not all the cells will contain a number, but the cells that do have a value, it will be the same value throughout those cells.

Otherwise I would have a single static cell to address. But I don't have that option.

I am asking in this fashion as it helps me to understand Excel better, rather than to have a sheet posted for someone to modify for me.

[Look at cell b4:h4, and if any of them have a number, is that number greater than 11? If so, then say True. If not, say false.]

Thanks for your help.

-Jeremy
 
I am asking in this fashion as it helps me to understand Excel better, rather than to have a sheet posted for someone to modify for me.
Click to expand...

IF you had read the rules you would have read:-
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
  • For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.
  • To share Files/Pictures, you can use the "Upload a File" button.
  • Please use language which conveys respect, appreciation and love.

It is not about file modification but allowing members to fully understand the problem with real data.
You can still learn, that's why the forum is here, by members having true data in the shape of an upload, other members do not have a problem with this, is as some times the data is confidential them alter what is need but keep it true as so as not to alter data.
 
I did read the rules, thank you. I will upload the file as I am able. My apologies. This isn't about confidential data, thank you for your inference.
 
Included is a file example of what I'm trying to do. Sorry for offending bobhc.

On the last sheet is a quick note of what I'm trying to do.
As I stated before, supplying an entire answer is appreciated, but certainly not necessary. If you'd like to offer what I should focus on, it would help me learn better. Thank you.
 

Attachments

  • test vacation tracker.xlsx
    362.7 KB · Views: 4
Last edited:
You need to supply further explanation...
  • Which sheet contains range that formula should reference, and what range
  • Which sheet and what range should contain the formula
  • What is the expected result
Etc.
 
My apologies... the note is in sheet 'Shift finder', J4.
I am looking for the formula to be in the same sheet. I would like it to look at a range of days (B:H), (of 7 cells, only 5 will have data, and will be the same). I'm looking to identify the start time. From there I can determine what I want the sheets to do next.

J5 would look at cells B5:H5, find out what the two digit number is (which represents the starting hour of their clock-in time) and determine what shift they work. If they start anywhere between 03 through whatever is in cell I5 minus an hour, they would be labeled as AM shift. Anytime from I5 forward would be identified as PM shift.

Does this help?
 
=IFERROR(CHOOSE(--(VALUE(INDEX(B5:H5,MIN(IF(B5:H5<>"",COLUMN(B5:H5)-1))))>=VALUE(LEFT($I$4,2)))+1,"AM","PM"),"")

So lets first look at INDEX() portion.

INDEX(B5:H5,MIN(IF(B5:H5<>"",COLUMN(B5:H5)-1)))

It looks for B5:H5 range, and IF will return column # when condition is met.
B5:H5<>"" will return array
{FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}

So, resolving when True with COLUMN(B5:H5)-1...
{FALSE,2,3,4,5,6,7}

MIN({FALSE,2,3,4,5,6,7}) = 2

INDEX(B5:H5,2) = 04

However, this is in text format so it's nested in VALUE() to turn it into number.

So formula is now...
=IFERROR(CHOOSE(--(4>=VALUE(LEFT($I$4,2)))+1,"AM","PM"),"")

$I$4 has value 1100 stored. But we only want 2 characters from left for the check and need it in number format.
VALUE(LEFT($I$4,2)) = 11

So 4>=11 is FALSE. Double "-" is used to turn TRUE/FALSE into number.
TRUE = 1, FALSE = 0

So formula is now...
=IFERROR(CHOOSE(0+1,"AM","PM"),"")

CHOOSE function is used to choose from choices, using index number.
CHOOSE(1, "AM", "PM")
In this instance, 1 will evaluate to "AM", if it was 2 then "PM".

Lastly, IFERROR is used to return blank ("") when all cells in range are blank.
 
Another option,

In J5, array formula (confirm entered with SHIFT+CTRL+ENTER) copy down :

=IF(COUNTIF(B5:H5,"?*"),IF(MAX(IFERROR(0+B5:H5,0))>=0+LEFT(I$4,2),"PM","AM"),"")

Regards
Bosco
 

Attachments

  • test vacation tracker (2).xlsx
    366.6 KB · Views: 6
Wow, thanks so much for explaining on how it works! I will study this more later, about to catch a flight. Thanks to you also Bosco, your input is much appreciated!

Jeremy
 
You must log in or register to reply here.
Back
Top