Excel 2007 : Future Date Formulas and Adjacent Cell Formulas

**thetaylor13** · 02-09-2011, 02:14 PM

So I basically no nothing about Excel formulas, but I've caught on doing basic stuff throughout the day, and now I'm at the trick part where I need some help.

First Issue
One of my cells, say A1, reflects a date that I type in. In return I would like cell B1 to auto-populate a date 7 days later than the A1 date.

Second Issue
One of my cells, say A1, contains a drop down list with 2 options, Yes and No. When I select "Yes" in cell A1 I would like cell B2 to basically "gray" out so no one tries to type anything in B2 if they selected "yes" for A1.

These are both way easier than what I'm making it seem, but I've given myself a headache with it so far!

Thanks in advance for any help and I apologize if I've repeated a past thread, I promise I tried searching.

**MarvinP** · 02-09-2011, 02:36 PM

Hi thetaylor13 and welcome to the forum.

For your first question - If 2/9/2011 is in A1, in B1 type "=A1 + 7" and press enter. That should show you the date 7 days in the future. I rarely does though, and that is why you did this post. You need to format the Cell B1 as date (if it doesn't). If it still doesn't then don't put the quote markes around the equation above.

For you second question. It is too hard to do for now. Some people would make the font of the answer the same as the background so it wouldn't show. Others would write some VBA code behind the workbook to Lock the cell. All that is down the road for you.

Keep learning how to input formulas. Have you discovered clicking on the "fx" next to the formula bar? This will give you help with formulas and even show all the different formulas and what extra things they want.

hope this helps.

**thetaylor13** · 02-09-2011, 04:00 PM

Marvin!

Perfect! I had tried the A1+7 with no avail, so thanks for pointing out it needed to be formatted for Date, works perfectly now! So that's all set thanks so much!

As for the other issue, thanks for the input, I can hold off on that one then until I become slightly more proficient.

As a side note, I have a second question if you have a chance.

With the first issue, switching the date in cell A1 to be 7 days ahead in B1, is there a quick way to assign this to more cells, so that A2 will affect B2, and A3 to C3, ect.

I know you can copy and paste the formulas but in this case if I tried that, every cell below B1 would simply be reflecting the formula based on A1. Or do I need to type in a new formula for each cell?

THANKS SO MUCH FOR THE HELP!!

**MarvinP** · 02-09-2011, 04:12 PM

Hi and GREAT QUESTION!!!!

In excel formulas are tied to Absolute or Relative References from the cell they are in. You have no idea what headaches this has given me in the past. If you put a dollar sign ($) in front of the letter or number it ties the formula Absolutely to that row or column.

For example a formula of = $A$1 +7 will always use the date in the top left cell, no matter where the formula is placed in the spreadsheet.
And the formula of = $A1 + 7 will use always use the column A to find it's source.
Then the formula of = A1 +7 is completely relative to where you put the formula.

Now that I have completely confused you about dollars signs and not being related to money, read these:
http://www.articlesbase.com/software...ng-572767.html or
http://web.pdx.edu/~stipakb/CellRefs.htm

Note - we all make mistakes with this from time to time.

Welcome to learning about the power of Excel.