Duplicate value to another sheet when two variables are met.

[JRegal]

Any help would be appreciated.

I have a set of dates that run across a row horizontally.
Under the dates I enter specific values that go along with the dates.
Example:

DATES:8/12/14 l 8/13/14 l 8/14/14 l 8/15/14 l 8/16/14
ENTERED VALUES: HK299 HK300 HK301

I must drag/extend the dates along the row occasionally and add new values.

In a separate sheet I would like to have the same format and I would like the values to be duplicated under dates as well.

I could use a simple formula to do this. But the tricky part is that I would like to create a static cell in the second sheet in which I could enter a specific value/variable such as (-4,-3,-2,-1,0,1,2,3,4, etc.) that would adjust or shift the values so they appear under different dates.

So for example:
+/- Day
Variable: 1 <--- This variable will be changed from time to time.
DATES:8/12/14 l 8/13/14 l 8/14/14 l 8/15/14 l 8/16/14 l 8/17/14
VALUES: HK299 HK300 HK301

I want the values on the first sheet to shift over by one day on the second sheet. If the variable was 2 then i would want the value to shift by 2 days.

 

[NeonRedSharpie]

So, you actually didn't change your sample data.......but I think I understand what you mean.


HLOOKUP should do the trick. I would think something like:

=HLOOKUP(A2+$A$1,Sheet1!$A$1:$F$2,2,0)

Where A1 is your variable
A2 is your date

sheet1!A1:F2 is your original table.

 

[JRegal]

A lot simpler then I imagined!!
Thank you!

 

[NeonRedSharpie]

A lot simpler then I imagined!!
Thank you!

Be warned, when the date is unavailable (with negatives near the left or positives near the right) it will return an #N/A so you'll probably want to wrap it in an iferror statement.

 

[JRegal]

Is there a way when I enter my variable for it to skip over certain dates?

If i enter a value under Friday - 9/12/2014 (say its HK299) and my variable is a +2 on the second sheet I want my value to skip over Saturday (9/13/2014) and Sunday (9/14/2014) so that "HK299" shows up under Tuesday - (9/16/2014)