Press a cell and then create cells with specific formula

U

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
496
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
i have sheet that just list of customers, i insert costumers name in one column and other data i want to have in next columns
for name of customers of have this formula : =HYPERLINK("#'Sheet name'!A"&MATCH("Name of Customer",Sheet name!A:A,0),"Name of Customer")
sheet name that is sheet customer that belong data in it, for example for me Work for sheet name
another formula this is in next columns
=IFERROR(INDEX(Sheet name!B:B,MATCH($I4,Sheet name!A:A,0)-2,0),"")
=IFERROR(SUM(INDEX(Sheet name!D:D,MATCH($I3,Sheet name!A:A,0)+1,0):INDEX(Sheet name!E:E,MATCH($I4,Sheet name!A:A,0)-2,0)),"")
=IFERROR(SUM(INDEX(Sheet name!F:F,MATCH($I3,Sheet name!A:A,0)+1,0):INDEX(Sheet name!G:G,MATCH($I4,Sheet name!A:A,0)-2,0)),"")
this 3 formulas drag and fill in next rows
now
i want when select cell after last customer that may write NEW CUSTOMER, create a new first formula with the name that i write and then drag and fill this 3 formulas in that row and still stay this cell NEW CUSTOMER after that...
 

Attachments

  • Screenshot 2021-03-27 180521.png
    Screenshot 2021-03-27 180521.png
    7 KB · Views: 18
  • Screenshot 2021-03-27 180524.png
    Screenshot 2021-03-27 180524.png
    11.6 KB · Views: 16

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Please upload example file & describe on it what exact you want. e.g. when select A10 copy formula from row 9 to row 10 & ....
Is this correct? you want to autofill all formula at last row (except New Customer Row) when select New Customer.
 
Upvote 0
And How about this.
Right click on sheet name then select view code & Paste this:
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim i As Long, j As Long, Lr As Long
 Lr = Range("A" & Rows.Count).End(xlUp).Row
  If Not Intersect(Target, Range("A" & Lr)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("A" & Lr).EntireRow.Insert
     Range("A" & Lr - 1 & ":D" & Lr - 1).AutoFill Destination:=Range("A" & Lr - 1 & ":D" & Lr)
    Application.EnableEvents = True
   End If
  End If
End Sub
 
Upvote 0
maabadi said:
And How about this.
Right click on sheet name then select view code & Paste this:
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, j As Long, Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
  If Not Intersect(Target, Range("A" & Lr)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("A" & Lr).EntireRow.Insert
     Range("A" & Lr - 1 & ":D" & Lr - 1).AutoFill Destination:=Range("A" & Lr - 1 & ":D" & Lr)
    Application.EnableEvents = True
   End If
  End If
End Sub
Click to expand...
Yeah, That's right, but anyway when doing this function i write name of customer and after that insert hyperlink formula?
for example in this code drag and fill with name Customer A, but i want when click new customer i write name of customer for example Customer B and after that this insert in hyperlink formula and drag and fill next formulas
 
Upvote 0
and i have new customer in 2 range, new customer in column I and end drag and fill in L and next new customer in N and end in Q
 
Upvote 0
maabadi said:
Please upload example file & describe on it what exact you want. e.g. when select A10 copy formula from row 9 to row 10 & ....
Is this correct? you want to autofill all formula at last row (except New Customer Row) when select New Customer.
Click to expand...
 
Upvote 0
maabadi said:
Please upload example file & describe on it what exact you want. e.g. when select A10 copy formula from row 9 to row 10 & ....
Is this correct? you want to autofill all formula at last row (except New Customer Row) when select New Customer.
Click to expand...
please check this
drive.google.com

Book1.xlsx

drive.google.com drive.google.com
 
Upvote 0
This do what you want And Add one empty Cell Before New customer Always ( at your Uploaded file).
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long
 Lr1 = Range("I" & Rows.Count).End(xlUp).Row
 Lr2 = Range("N" & Rows.Count).End(xlUp).Row
  If Not Intersect(Target, Range("I" & Lr1)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("I" & Lr1 & ":M" & Lr1).Insert Shift:=xlDown
    Application.EnableEvents = True
   End If
  End If
    If Not Intersect(Target, Range("N" & Lr2)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("N" & Lr2 & ":Q" & Lr2).Insert Shift:=xlDown
    Application.EnableEvents = True
   End If
  End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Cr1 As Long, Cr1R As String, Cr2 As Long, Cr2R As String
Dim Lr3 As Long, Lr4 As Long
 Lr1 = Range("I" & Rows.Count).End(xlUp).Row - 1
 Lr2 = Range("N" & Rows.Count).End(xlUp).Row - 1
 Lr3 = Sheets("Work").Range("A" & Rows.Count).End(xlUp).Row
 Lr4 = Sheets("Paper").Range("A" & Rows.Count).End(xlUp).Row
 On Error Resume Next
 Cr1 = Application.WorksheetFunction.Match(Range("I" & Lr1).Value, Sheets("Work").Range("A1:A" & Lr3), 0)
 Cr1R = Range("A" & Cr1).Address
 Cr2 = Application.WorksheetFunction.Match(Range("N" & Lr1).Value, Sheets("Paper").Range("A1:A" & Lr4), 0)
 Cr2R = Range("A" & Cr2).Address
  If Not Intersect(Target, Range("I" & Lr1)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("J" & Lr1 - 1 & ":L" & Lr1 - 1).AutoFill Destination:=Range("J" & Lr1 - 1 & ":L" & Lr1)
     Sheets("Dashboard").Hyperlinks.Add Anchor:=Range("I" & Lr1), Address:="", SubAddress:="'" & Sheets("Work").Name & "'!" & Cr1R, TextToDisplay:=Range("I" & Lr1).Value
    Application.EnableEvents = True
   End If
  End If
    If Not Intersect(Target, Range("N" & Lr2)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("O" & Lr2 - 1 & ":Q" & Lr2 - 1).AutoFill Destination:=Range("O" & Lr2 - 1 & ":Q" & Lr2)
     Sheets("Dashboard").Hyperlinks.Add Anchor:=Range("N" & Lr2), Address:="", SubAddress:="'" & Sheets("Paper").Name & "'!" & Cr2R, TextToDisplay:=Range("N" & Lr2).Value
    Application.EnableEvents = True
   End If
  End If
End Sub
 
Upvote 0
maabadi said:
This do what you want And Add one empty Cell Before New customer Always ( at your Uploaded file).
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long
Lr1 = Range("I" & Rows.Count).End(xlUp).Row
Lr2 = Range("N" & Rows.Count).End(xlUp).Row
  If Not Intersect(Target, Range("I" & Lr1)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("I" & Lr1 & ":M" & Lr1).Insert Shift:=xlDown
    Application.EnableEvents = True
   End If
  End If
    If Not Intersect(Target, Range("N" & Lr2)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("N" & Lr2 & ":Q" & Lr2).Insert Shift:=xlDown
    Application.EnableEvents = True
   End If
  End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Cr1 As Long, Cr1R As String, Cr2 As Long, Cr2R As String
Dim Lr3 As Long, Lr4 As Long
Lr1 = Range("I" & Rows.Count).End(xlUp).Row - 1
Lr2 = Range("N" & Rows.Count).End(xlUp).Row - 1
Lr3 = Sheets("Work").Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sheets("Paper").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Cr1 = Application.WorksheetFunction.Match(Range("I" & Lr1).Value, Sheets("Work").Range("A1:A" & Lr3), 0)
Cr1R = Range("A" & Cr1).Address
Cr2 = Application.WorksheetFunction.Match(Range("N" & Lr1).Value, Sheets("Paper").Range("A1:A" & Lr4), 0)
Cr2R = Range("A" & Cr2).Address
  If Not Intersect(Target, Range("I" & Lr1)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("J" & Lr1 - 1 & ":L" & Lr1 - 1).AutoFill Destination:=Range("J" & Lr1 - 1 & ":L" & Lr1)
     Sheets("Dashboard").Hyperlinks.Add Anchor:=Range("I" & Lr1), Address:="", SubAddress:="'" & Sheets("Work").Name & "'!" & Cr1R, TextToDisplay:=Range("I" & Lr1).Value
    Application.EnableEvents = True
   End If
  End If
    If Not Intersect(Target, Range("N" & Lr2)) Is Nothing Then
   If Target.Count = 1 Then
    Application.EnableEvents = False
     Range("O" & Lr2 - 1 & ":Q" & Lr2 - 1).AutoFill Destination:=Range("O" & Lr2 - 1 & ":Q" & Lr2)
     Sheets("Dashboard").Hyperlinks.Add Anchor:=Range("N" & Lr2), Address:="", SubAddress:="'" & Sheets("Paper").Name & "'!" & Cr2R, TextToDisplay:=Range("N" & Lr2).Value
    Application.EnableEvents = True
   End If
  End If
End Sub
Click to expand...
i test it, 2 things
1, anyway not empty cell before new customer? because of the 3 formula after name want last cell that fill with specific name (like in file i uploaded new customer and if empty cell between last customer and new customer, last customer specific formula show empty)
2, when i write a name for example Customer D, after that fill that cell name like this: =HYPERLINK("#'Work'!A"&MATCH("Customer D",Work!A:A,0),"Customer D") for sheet that linked Work and for Paper like this =HYPERLINK("#'Paper'!A"&MATCH("Customer D",Paper!A:A,0),"Customer D")
 
Upvote 0
1. it doesn't make problem. when you input new customer name then formula copied above row and cells filled.
2. For this, you only need to add Customer Name. I add Hyperlink to Cells without formula & with VBA method then when you Press them you go to target Cell. No Problem
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA - add formula to cell when created
Replies
2
Views
285
Golaidron
G
I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
414
ipbr21054
I
szita2000
  • Question
Index Match Match from a named table to a range
Replies
1
Views
272
Alex Blakenburg
A
B
  • Question
Copy formula into a cell using vba
2
Replies
11
Views
407
BobtBuilder
B
G
  • Solved
Extract the cells that contains alphanumeric string from the column
Replies
6
Views
365
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,525
Messages
6,120,052
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top