# Set a formula for a whole table based on the next nonempty cell to the left

#### OGGY67

##### New Member
I am trying to set up a schedule sheet made up of a set of dates based on the first column.
It would be easy, just adding the number of days as in this example, but from the second column there may be some empty cells and this varies each time a new schedule is set up. I don't want to use code, I would like to use functions so that anyone can modify it in case it is needed. Somebody said using INDEX and MATCH function I can do it, but I don´t get it. Any ideas?
1 A B C D
2 date =(A1+\$A\$7) =(B1 +\$A\$7) =(C1 +\$A\$7)
3 date =(A2+\$A\$7) =(B2 +\$A\$7)
4 date =(A3+\$A\$7) =(B3 +\$A\$7) =(C3 +\$A\$7)
5 date =(A4+\$A\$7) =(B4 +\$A\$7) =(C4 +\$A\$7)
6 date =(A5+\$A\$7) =(B5 +\$A\$7)
7
8 #days

Last edited by a moderator:

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### OGGY67

##### New Member
Correction to the previous post because I can't edit it.
1-- A ---------B-------------- C-------------- D
2 date =(A2+\$A\$8) =(B2 +\$A\$8)
3 date =(A3 +\$A\$8) =(C3 + \$A\$8)
4 date =(A4+\$A\$8) =(B4 +\$A\$8) =(C4 +\$A\$8)
5 date =(A5+\$A\$8) =(B5 +\$A\$8)
6 date =(A6+\$A\$8) =(B6 +\$A\$8) =(C6 + \$A\$8
7
8 #days

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

You have had nearly 20 people look at you post and no replies. I suspect the other viewers, like me, don't really understand what you have and what you are trying to do.
Can you post some sample data and expected results with XL2BB and explain again in relation to that new sample data?

• OGGY67

#### bebo021999

##### Well-known Member
• OGGY67

#### OGGY67

##### New Member
I re-draw the whole picture follow what to be displayed. What is your expected outcome for this:
Cell Formulas
RangeFormula
B6:D6,B5:C5,B4:D4,B2:C3B2=A2+\$A\$8
A3:A6A3=A2+1
Thank you for your help
Everything moved the second I posted it, the row numbers are incorrect, I can´t install the XL2BB on this computer and also it won´t let me edit the post!
I will try again: If the cell to the left is empty, the formula should consider the next cell that is not empty to give you a new date based on \$A\$7.
Now my formulas are basically these, all different and I have to change them manually (empty cells may vary each time).
I need one formula for the whole table (it is huge) that anyone can just fill it down and then set the empty cells where needed, considering empty cells between them could be more than one. It would save tons of time for me and my team.
-- A ---------B----------- C------------ D-------------E
1 date =(A1+\$A\$7)--------------- =(B1 +\$A\$7) =(D1 +\$A\$7)
2 date =(A2 +\$A\$7)----------------------------- =(B1 +\$A\$7)
3 date =(A3+\$A\$7) =(B3 +\$A\$7) =(C3 +\$A\$7) =(D1 +\$A\$7)
4 date =(A4+\$A\$7)--------------- =(B4 +\$A\$7) =(B1 +\$A\$7)
5 date =(A5+\$A\$7) =(B3 +\$A\$7) =(C5 +\$A\$7) =(B1 +\$A\$7)
6
7 #days

#### OGGY67

##### New Member
I re-draw the whole picture follow what to be displayed. What is your expected outcome for this:
Cell Formulas
RangeFormula
B6:D6,B5:C5,B4:D4,B2:C3B2=A2+\$A\$8
A3:A6A3=A2+1
Thank you, this is what i meant to post:
-- A ---------B----------- C------------ D-------------E
1 date =(A1+\$A\$7)--------------- =(B1 +\$A\$7) =(D1 +\$A\$7)
2 date =(A2 +\$A\$7)----------------------------- =(B1 +\$A\$7)
3 date =(A3+\$A\$7) =(B3 +\$A\$7) =(C3 +\$A\$7) =(D1 +\$A\$7)
4 date =(A4+\$A\$7)--------------- =(B4 +\$A\$7) =(D1 +\$A\$7)
5 date =(A5+\$A\$7) =(B3 +\$A\$7) =(C5 +\$A\$7) =(D1 +\$A\$7)
6
7 #days

#### OGGY67

##### New Member
I am trying to set up a schedule sheet made up of a set of dates based on the first column.
It would be easy, just adding the number of days as in this example, but from the second column there may be some empty cells and this varies each time a new schedule is set up. I don't want to use code, I would like to use functions so that anyone can modify it in case it is needed. Somebody said using INDEX and MATCH function I can do it, but I don´t get it. Any ideas?
1 A B C D
2 date =(A1+\$A\$7) =(B1 +\$A\$7) =(C1 +\$A\$7)
3 date =(A2+\$A\$7) =(B2 +\$A\$7)
4 date =(A3+\$A\$7) =(B3 +\$A\$7) =(C3 +\$A\$7)
5 date =(A4+\$A\$7) =(B4 +\$A\$7) =(C4 +\$A\$7)
6 date =(A5+\$A\$7) =(B5 +\$A\$7)
7
8 #days
I better post an image #### OGGY67

##### New Member
Welcome to the MrExcel board!

You have had nearly 20 people look at you post and no replies. I suspect the other viewers, like me, don't really understand what you have and what you are trying to do.
Can you post some sample data and expected results with XL2BB and explain again in relation to that new sample data?
I just posted a new correction and an image of the table. I can´t install XL2BB on my laptop. I need this so badly. Thank you!

#### OGGY67

##### New Member
I am trying to set up a schedule sheet made up of a set of dates based on the first column.
It would be easy, just adding the number of days as in this example, but from the second column there may be some empty cells and this varies each time a new schedule is set up. I don't want to use code, I would like to use functions so that anyone can modify it in case it is needed. Somebody said using INDEX and MATCH function I can do it, but I don´t get it. Any ideas?
1 A B C D
2 date =(A1+\$A\$7) =(B1 +\$A\$7) =(C1 +\$A\$7)
3 date =(A2+\$A\$7) =(B2 +\$A\$7)
4 date =(A3+\$A\$7) =(B3 +\$A\$7) =(C3 +\$A\$7)
5 date =(A4+\$A\$7) =(B4 +\$A\$7) =(C4 +\$A\$7)
6 date =(A5+\$A\$7) =(B5 +\$A\$7)
7
8 #days
Here is an image on the table, but formula is very complicated, so I simplified it. I only care about the way you can identify the next cell is empty or not to consider it as a base for the next date. #### OGGY67

##### New Member
Thank you for your help
Everything moved the second I posted it, the row numbers are incorrect, I can´t install the XL2BB on this computer and also it won´t let me edit the post!
I will try again: If the cell to the left is empty, the formula should consider the next cell that is not empty to give you a new date based on \$A\$7.
Now my formulas are basically these, all different and I have to change them manually (empty cells may vary each time).
I need one formula for the whole table (it is huge) that anyone can just fill it down and then set the empty cells where needed, considering empty cells between them could be more than one. It would save tons of time for me and my team.
-- A ---------B----------- C------------ D-------------E
1 date =(A1+\$A\$7)--------------- =(B1 +\$A\$7) =(D1 +\$A\$7)
2 date =(A2 +\$A\$7)----------------------------- =(B1 +\$A\$7)
3 date =(A3+\$A\$7) =(B3 +\$A\$7) =(C3 +\$A\$7) =(D1 +\$A\$7)
4 date =(A4+\$A\$7)--------------- =(B4 +\$A\$7) =(D1 +\$A\$7)
5 date =(A5+\$A\$7) =(B3 +\$A\$7) =(C5 +\$A\$7) =(D1 +\$A\$7)
6
7 #days
I posted an image of the table on my first post. Thank you!

Replies
3
Views
180
Replies
2
Views
100
Replies
1
Views
110
Replies
2
Views
235
Replies
3
Views
118

### Forum statistics

1,175,524
Messages
5,897,919
Members
434,687
Latest member
alybazaza ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back