Linking Cells between worksheet

Question

• i want to link data between two Sheets based on the month(Jul - Jun). Sheet 1 and Sheet 2.

Sheet 1 contains the final result

Sheet 2 contains the raw data.

On sheet1, cell B3:B6. I have this formula:

B3 =IF(LEFT(MONTH('sheet2'!D23),2)="7",'sheet2'!E33, "")

B4  =IF(LEFT(MONTH('sheet2'!D23),2)="7",'sheet2'!E35, "")

B5  =IF(LEFT(MONTH('sheet2'!D23),2)="7",'sheet2'!E38, "")

B6  =IF(LEFT(MONTH('sheet2'!D23),2)="7",'sheet2'!E40, "")

How do I prevent the values entered in July from Changing when I enter the new value for August?

Is there a better and cleaner method to do this because I will need to link all the cells with  the above formula from July to June?

If there is a  better way, how do I do it?

Thanks!!!!

Answer 1

Just added a few lines at the end to compete the code; and made Mth numeric

 Sub MthlyFuelUtility()

 Dim Mth As Integer

 Dim UF_Data As Worksheet

 Set UF_Data = Sheets("sheet2")

 UF_Data.Select

  Mth = Month(Range("d23"))

 Sheets("sheet1").Select

 If Mth = 7 Then

 Range("E22") = UF_Data.Range("E33")

 Range("E23") = UF_Data.Range("E36")

 Range("E24") = UF_Data.Range("E39")

 Range("E25") = UF_Data.Range("E42")

 ElseIf Mth = 8 Then

 Range("F22") = UF_Data.Range("E33")

 Range("F23") = UF_Data.Range("E36")

 Range("F24") = UF_Data.Range("E39")

 Range("F25") = UF_Data.Range("E42")

 ElseIf Mth = 9 Then

 Range("G22") = UF_Data.Range("E33")

 Range("G23") = UF_Data.Range("E36")

 Range("G24") = UF_Data.Range("E39")

 Range("G25") = UF_Data.Range("E42")

 ElseIf Mth = 10 Then

 Range("H22") = UF_Data.Range("E33")

 Range("H23") = UF_Data.Range("E36")

 Range("H24") = UF_Data.Range("E39")

 Range("H25") = UF_Data.Range("E42")

 ElseIf Mth = 11 Then

 Range("I22") = UF_Data.Range("E33")

 Range("I23") = UF_Data.Range("E36")

 Range("I24") = UF_Data.Range("E39")

 Range("I25") = UF_Data.Range("E42")

 Else

 MsgBox ("Error")

 End If

 End Sub

Then I dis some more tiding up

 Sub MonthlyFuelUtility()

 Dim Mth As Integer

 Set UF_Data = Sheets("sheet2")

  Mth = Month(UF_Data.Range("d23"))

  Sheets("sheet1").Activate

  Select Case Mth

   Case 7

   Range("E22") = UF_Data.Range("E33")

   Range("E23") = UF_Data.Range("E36")

   Range("E24") = UF_Data.Range("E39")

   Range("E25") = UF_Data.Range("E42")

   Case 8

   Range("F22") = UF_Data.Range("E33")

   Range("F23") = UF_Data.Range("E36")

   Range("F24") = UF_Data.Range("E39")

   Range("F25") = UF_Data.Range("E42")

  Case 9

   Range("G22") = UF_Data.Range("E33")

   Range("G23") = UF_Data.Range("E36")

   Range("G24") = UF_Data.Range("E39")

   Range("G25") = UF_Data.Range("E42")

  Case 10

   Range("H22") = UF_Data.Range("E33")

   Range("H23") = UF_Data.Range("E36")

   Range("H24") = UF_Data.Range("E39")

   Range("H25") = UF_Data.Range("E42")

 Case 11

   Range("I22") = UF_Data.Range("E33")

   Range("I23") = UF_Data.Range("E36")

   Range("I24") = UF_Data.Range("E39")

   Range("I25") = UF_Data.Range("E42")

 Case Else

   MsgBox ("Error")

 End Select

 End Sub

best wishes

Answer 2

Thanks Alot!!!

I am now trying to use a macro to achieve this process. 

Is there a short way to achieve the below?

The below code works

Thanks!

 Sub MthlyFuelUtility()

Dim Mth As String

Dim UF_Data As Worksheet

Set UF_Data = Sheets("sheet2")

UF_Data.Select

 Mth = Month(Range("d23"))

Sheets("sheet1").Select

If Mth = "7" Then

Range("E22") = UF_Data.Range("E33")

Range("E23") = UF_Data.Range("E36")

Range("E24") = UF_Data.Range("E39")

Range("E25") = UF_Data.Range("E42")

ElseIf Mth = "8" Then

Range("F22") = UF_Data.Range("E33")

Range("F23") = UF_Data.Range("E36")

Range("F24") = UF_Data.Range("E39")

Range("F25") = UF_Data.Range("E42")

ElseIf Mth = "9" Then

Range("G22") = UF_Data.Range("E33")

Range("G23") = UF_Data.Range("E36")

Range("G24") = UF_Data.Range("E39")

Range("G25") = UF_Data.Range("E42")

ElseIf Mth = "10" Then

Range("H22") = UF_Data.Range("E33")

Range("H23") = UF_Data.Range("E36")

Range("H24") = UF_Data.Range("E39")

Range("H25") = UF_Data.Range("E42")

ElseIf Mth = "11" Then

Range("I22") = UF_Data.Range("E33")

Range("I23") = UF_Data.Range("E36")

Range("I24") = UF_Data.Range("E39")

Range("I25") = UF_Data.Range("E42")

Else

MsgBox ("Error")

End If

Answer 3

Cell D23 shows  the date 7/1/17

There is no need to use text function like LEFT to discover the month

Below, in row 3 I have dates 7/1/17,8/1/17, etc. But these cells have custom format mmm so they show the month name. Use custom format mmmm to get full name.

The formula in C4 does more of less what your B3 formulas does

best wishes

Answer 4

This is the raw data in sheet2 that i have to populate in sheet1. the section in green and orange. I only showed the data in orange on sheet1.

The green will be the same process once i figure out the orange.

Thanks!!!

 

Answer 5

Please show us some of the data on Sheet2

best wishes