If Function to Copy a different cell value into another cell and change different cell value

[yazanibrahim1984]

Hello,

My first post because I usually find my answers without asking

So here's my issue:

I'm trying to create a formula, using IF Function maybe??, that copies certain cells from one sheet to different cells in another sheet and change a cell value on the first sheet, but the cells are not in the same order and their

Each sheet has 1700+ rows and about 47 columns

Let's take the below as sample:

Sheet 2 Cell J2 has the text YES
Sheet 2 Cell A2 has text
Sheet 2 Cell B2 has numbers
Sheet 2 Cell C2 has date
Sheet 2 Cell D2 has a number

I'm writing a formula in Sheet1 Cell A2 that says:=if(Sheet2!J2="yes",... but I also want to copy Sheet2 cell A2 in Sheet1 Cell B2 and Copy Sheet2 Cell B2 into Sheet1 E4 and Copy Sheet2 Cell C2 into Sheet1 Cell G2 aaaaaand I want to set the value of D2 to 0.00

Is this monster possible?

 

[Fluff]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

Hi & welcome to MrExcel.
A formula can only affect the cell that it's in, so to change multiple cells, you will need a formula in each of them.

 

[yazanibrahim1984]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

Thanks for the welcome, but doesn't sound a bit excessive to have a formula in each cell?

 

[Fluff]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

It's either that or VBA.
As I said a formula can only affect the cell it's in.

 

[Joe4]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

but doesn't sound a bit excessive to have a formula in each cell?

No, not at all, if you want a value to be returned in each cell. That is the way Excel works.
In order to return a value in each cell, you either need to hard-code it in there, or have a formula in that cell that returns the value you are looking for.

If you want to assign a value to a certain cell without manually typing it in (or using copy/paste), or using a formula in that cell, that would require VBA code to copy or assign something to those cells.

 

[yazanibrahim1984]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

I see... any suggestion how to code that in VBA?

 

[yazanibrahim1984]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

I figured that was the case, because I tried IF functions in many different ways using AND & OR function, but nothing worked

 

[Fluff]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

In order to supply a macro we would need to no exactly what needs to happen & when

 

[yazanibrahim1984]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

Well this is what I wish to exactly happen:

1. If Cell K6 = Yes on Sheet2 then Make the value in Cell E6 = 0.00 on Sheet2

2. If Cell K6 = Yes on Sheet2 then Change the text in Cell A3 to Processed on Sheet1

3. If Cell K6 = Yes on Sheet2 then Copy the text in Cell A6 on Sheet2 to Cell B3 on Sheet1

4. If Cell K6 = Yes on Sheet2 then Copy the text in Cell D6 on Sheet2 to Cell E3 on Sheet1

5. If Cell K6 = Yes on Sheet2 then Copy the text in Cell F6 on Sheet2 to Cell F3 on Sheet1

6. If Cell K6 = Yes on Sheet2 then Copy the text in Cell I6 on Sheet2 to Cell D3 on Sheet1

 

[Fluff]

Re: IF Function Gurus - If Function to Copy a different cell value into another cell and change different cell value

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "K6" Then
      If Target.Value = "Yes" Then
         Range("E6") = 0
         With Sheets("Sheet1")
            .Range("A3").Value = "Prcoessed"
            .Range("B3") = Range("A6")
            .Range("E3") = Range("D6")
            .Range("F3") = Range("F6")
            .Range("D3") = Range("I6")
         End With
      End If
   End If
End Sub

This needs to do in the Sheet2 code module and will run automatically whenever you change K6