Nested IFs

[padadof2]

I would like to have excel look at column b and return the values in column a if there is a value in column b.

Here is what started with, but I am very new at excel.

=IF(ISTEXT(Schedule!B12),Schedule!A12,(IF(ISTEXT(Schedule!B16),Schedule!A16)))

I am trying to do this for 8 rows and only want it to populate if there is a value in column B

I can get it to populate with blanks, but I would like it only to populate when there is a value.

Thanks in advance.

[NBVC]

So you are looking first at B12 and if there is something, you want A12, else you want to look at B16 and if there is something, then A16, else leave it blank?

[padadof2]

I would like it to look at a15 then b 16 then c 16, etc....then only populate if there is a value.

I have attached the spreadsheet, so you can see what I am trying to do.

I want to learn how to do it, but this is a little over my head.

Thanks a lot for looking at this.

[JBeaucaire]

1) I converted all the "totals" into normal decimal values since 9 times out of 10 that's the way they need to be evaluated. So instead of 7:30:00 for a total hours, it now reads normally as 7.5 hours.

2) I added a set of "key index" columns off to the right of the schedule. These formulas flag specific rows for each day of the week and do it in a way that doesn't require heavy array formulas on all your daily sheets. The simple numbering system allows the names and times to be taken directly from the schedule in a simple way.

These columns can be hidden if desired.

3) All the day sheets now have an index in column A and the values matching the KEY columns from the schedule make it easy to INDEX/MATCH them over onto each sheet.

This should be very robust for you.

[JBeaucaire]

Please download the sheet again, I've edited it to include the hidden rows on the Schedule I didn't notice the first time.

1:56 PM also added some automation at the top of the schedule so you only need enter the starting date.

[padadof2]

this is GREAT! thanks a bunch! This is exactly what I was looking for. I am still trying to get my head around the keys on the side and how they work. This is definately solved, but I am just hoping to understand how it worked. Thanks again!

[JBeaucaire]

This formula in L12:
=IF(AND(ISNUMBER(B12),ISTEXT($A12)),L11+1,L11)

...makes the following requirements to increment:

1) B12 must have a number in it (it is blank, so this formula will fail and remain zero)
2) $A12 must have a text in it (Has "Mike" so this works)
3) If 1 & 2 are both true, then L12 will look up to L11 and add 1. If not, it reflects the value in L11 again.

So, only the first row of 4-row group can increment because I've removed the extra text from column A and there is only text in the first row of each group. (notice I took out the word "total"?).

The indexes on the various pages are matching the first instance of each index number on the schedule in the appropriate key column to the index in column A of the daily sheet. This simple match makes it easy to find the row of data to display on each line.
=========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[padadof2]

That was a little over my head, but I do see what you were doing. Hows the weather out there in BK? I have family in Kernville, go there often!

[JBeaucaire]

Hehe...weather here is always great...gotta love So. California!

My wife is from PA. Small world...