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

OGGY67

New Member
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
56,480
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Captura.PNG
 

OGGY67

New Member
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
Captura2.PNG
 

OGGY67

New Member
Joined
Mar 1, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top