Automatically fill in data based on multiple criteria from another spreadsheet

Hi Mike,

Thanks so much for taking the time out to look at this. I've spent 2 days on it and have no idea what I'm doing!

See new attachment:

I want Sheet 1 to pull data from Sheet 2.

I want the macro to automatically fill in the Sheet 1 to look like Sheet 3.

The revenue on Sheet 1 should start 2 months after the start month. In the example, Start month of Jan-15, sheet should fill in
revenue for Mar-15 through Dec-15.

Thank you, but it did not work. I want the revenue to fill in 2 months after the start month. So if Sheet 2 says 15-Jan, then it should start 15-March on Sheet 1. Also I need the rest of the months to be filled in. So if it says 15-Jan on Sheet 2, then 15-Mar through 15-Dec should be filled in on Sheet 1.

Hi XLAdept,

I will try it now. Will let you know. Thank you!

Trinh

Originally Posted by xladept

Replace the event code with this:

Private Sub Worksheet_Change(ByVal Target As Range): Dim r As Long, c As Long
        Dim N As String, D As String, M As Single, w1 As Worksheet
r = Rows.Find("*", , , , xlByRows, xlPrevious).Row: Set w1 = Sheets("Sheet 1")
If WorksheetFunction.CountA(Cells(Target.Row, 1).Resize(1, 3)) = 3 Then
If Target.Column < 4 And Target.Row <= r + 1 Then
r = Target.Row: D = Cells(r, 3): M = Cells(r, 2): N = Cells(r, 1)
r = 2: Do Until w1.Cells(r, 1) = N Or w1.Cells(r, 1) = "": r = r + 1: Loop
c = w1.Rows(1).Find(D).Column + 2: w1.Cells(r, 1) = N
w1.Cells(r, c).Resize(1, 14 - c) = M
End If: End If: End Sub

And - Thanks for the rep!

It worked for "Client 4", but how do I get it to generate data for the other clients and any new client I add to the sheet?! LOL

Here's the attachment with the updated code.

XLAdept, any luck?

hanks for the suggestion. I have renamed my thread (not sure I named it correctly LOL). Hopefully I can get someone to help me! I am so stressed! Here's the link to my thread as you requested: http://www.excelforum.com/excel-prog...ml#post3986322

Trinh

Originally Posted by FDibbins

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I meant to say Thanks! not "hanks" LOL

http://www.excelforum.com/excel-prog...ml#post3986322

I'm able to add Client 5 too (thank you ) , but for some reason it's not generating data for Clients 1-3 on sheet 1.... I'm sorry to be such a pain.

Originally Posted by xladept

Hey Trinh,

You have to enter all three data before it takes hold - I just added a client 5 with no problem

Here's the attachment. Clients 1, 2, and 3 doesn't have data.

Originally Posted by xladept

Private Sub Worksheet_Change(ByVal Target As Range): Dim r As Long, c As Long
        Dim N As String, D As String, M As Single, w1 As Worksheet
r = Rows.Find("*", , , , xlByRows, xlPrevious).Row: Set w1 = Sheets("Sheet1")
If WorksheetFunction.CountA(Cells(Target.Row, 1).Resize(1, 3)) = 3 Then
If Target.Column < 4 And Target.Row <= r + 1 Then
r = Target.Row: D = Cells(r, 3): M = Cells(r, 2): N = Cells(r, 1)
r = 2: Do Until w1.Cells(r, 1) = N Or w1.Cells(r, 1) = "": r = r + 1: Loop
If D = "Existing" Then
c = 2
Else: c = w1.Rows(1).Find(D).Column + 2: End If
w1.Cells(r, c).Resize(1, 14 - c) = M: w1.Cells(r, 1) = N
End If: End If: End Sub

I used what you taught me to create the attached spreadsheet. It works like a charm and I have all the data loaded. Everything looks good EXCEPT I wanted to test adding a new client. When I try to add a new client now or run the macro, it comes up with an error. I am pretty sure I messed up your code somewhere. Can you take one final look? PLEASE : (

Originally Posted by xladept

In Column B you need to reenter the amount (or any column reenter its entry)

Thank you!!!! Worked like a charm. You are awesome!

Originally Posted by xladept

In Column B you need to reenter the amount (or any column reenter its entry)

Okay, last question. I promise! LOL. See attached highlighted in yellow. What if I want it to fill in for 15-Jan through 15-Dec on Sheet 1 if the start month is "existing" on Sheet 2. I manually added it to the sheet to show you what I need.

Originally Posted by xladept

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range): Dim r As Long, c As Long
        Dim N As String, D As String, M As Single, w1 As Worksheet
r = Rows.Find("*", , , , xlByRows, xlPrevious).Row: Set w1 = Sheets("Sheet1")
If WorksheetFunction.CountA(Cells(Target.Row, 1).Resize(1, 3)) = 3 Then
If Target.Column < 4 And Target.Row <= r + 1 Then
r = Target.Row: D = Cells(r, 3): M = Cells(r, 2): N = Cells(r, 1)
r = 2: Do Until w1.Cells(r, 1) = N Or w1.Cells(r, 1) = "": r = r + 1: Loop
If D = "Existing" Then
c = 2
Else: c = w1.Rows(2).Find(D).Column + 2: End If
w1.Cells(r, c).Resize(1, 14 - c) = M: w1.Cells(r, 1) = N
End If: End If: End Sub

*But there is no column for 6-15

It's adding the New Client in Row 2 instead of Row 56. See attached.

Thanks so much for today LOL. You have been awesome. I will try the new code and let you know.

Originally Posted by xladept

I updated the last post - try it again.

Signing off for the day

It works but it's adding it on row 60, instead of row 56. I was hoping to eliminate the space (empty rows) in between. See attached.

Talk to you tomorrow - Hopefully! : )

I really need help. I am a newbie to Excel and Macros. The previous code XLAdept gave me worked, but I changed my spreadsheet so now it's not working anymore.

See attached spreadsheet:CashFlowExample.xlsm

I need a code that generates data on Sheet 1 (Cash Flow 2015) based on the data on Sheet 2 (2015 Clients).

On Sheet 2, if column D says "Annual", then I want the Annual payment Amount (Column C) to generate on the "Expected Payment Date" (Column F) on sheet 1. For example, Boston EMS should generate $68,100 in Apr-15 on Sheet 1.

if column D says "Monthly", then I want the Monthly payment Amount (Column B) to generate 2 months from the "Start Month" (Column E) through Dec-15 (end of the year). For example, Blanchester EMS should generate $133 Mar-15 through Dec-15.

Essentially, I want the macro(s) to make Sheet 1 (Cash Flow 2015) to look like Sheet 3. Sheet 3 is only there for display purposes and will be deleted.

I want the sheet to also:

- Automatically update Sheet 1 if I add a customer to sheet 2.
- Automatically update Sheet 1 if I delete a customer on sheet 2.
- Automatically update Sheet 1 if I change the payment amounts in Columns B or C.
- Automaticaly update Sheet 1 if I change column D (Annual to Monthly or vice versa).

Is this going to be possible?