Subtract the value in the adjoining column
Closed
ninz
-
Apr 4, 2011 at 02:19 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 4, 2011 at 11:08 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 4, 2011 at 11:08 PM
Related:
- Subtract the value in the adjoining column
- Which function is used to compare a cell value to an array of cells and return a value that matches the location of the value in the array, and is used when there are more than two columns in the array ✓ - Excel Forum
- How to convert column to row in notepad++ ✓ - Excel Forum
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum
- Display two columns in data validation list but return only one - Guide
- Excel copy column from one sheet to another automatically - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 4, 2011 at 06:47 AM
Apr 4, 2011 at 06:47 AM
the url of your uploaded file is something wrong. it should contains the name of the file with extensions after .xom/
jack4rall
Posts
6428
Registration date
Sunday June 6, 2010
Status
Moderator
Last seen
July 16, 2020
Apr 4, 2011 at 04:37 PM
Apr 4, 2011 at 04:37 PM
Greetings ninz,
Please continue your discussion in one thread. There is no need to open a new thread all the time for the same question.
Regards
Please continue your discussion in one thread. There is no need to open a new thread all the time for the same question.
Regards
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 4, 2011 at 11:08 PM
Apr 4, 2011 at 11:08 PM
I hope I have understood what you want.
let us rephrase
download this file NINZ.xls from this url
http://www.speedyshare.com/files/27788514/NINZ.xls
In this file there is not data in the column Jun'10 column because there is no such data in sheet 1
now what you want is:
in the row Jun"10 in sheet1 suppose you fill the particulars and price you must get these data translated automatically in sheet 2
I am giving below an EVENT CODE . right click the sheet1 tab and click view code
you will see this event code there .
for testing
now in sheet1 against row Jun'10 type "files" in particulars column that is in B12 and some price e.g. 27 in C12 and hit enter
see what happens in sheet
the event code is already in the sheet code. still I am repeating that also
REMEMBER THAT THE WORD "FILES" OR OTHER PARTICULARS IF THERE IS SPELLING MISTAKE WHEN ENTERING IN SHEET 1 IT WILL GIVE A BUG. SO BE CAREFUL.
If there is a bug the code statement where the code stops and also error message if any may be given.
THE EVENT CODE IS
'----------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim part As String, price As Double, cfinddte As Range, dte As String, cfindpart As Range
If Target.Column <> 3 Then Exit Sub
price = Target.Value
part = Target.Offset(0, -1).Value
'MsgBox part
dte = Target.Offset(0, -2).Value
'MsgBox dte
With Worksheets("sheet2")
Set cfinddte = .Cells.Find(what:=dte, lookat:=xlWhole)
Set cfindpart = .Cells.Find(what:=part, lookat:=xlWhole)
Intersect(.Columns(cfinddte.Column), .Rows(cfindpart.Row)) = price
End With
End Sub
'------------------------------------
let us rephrase
download this file NINZ.xls from this url
http://www.speedyshare.com/files/27788514/NINZ.xls
In this file there is not data in the column Jun'10 column because there is no such data in sheet 1
now what you want is:
in the row Jun"10 in sheet1 suppose you fill the particulars and price you must get these data translated automatically in sheet 2
I am giving below an EVENT CODE . right click the sheet1 tab and click view code
you will see this event code there .
for testing
now in sheet1 against row Jun'10 type "files" in particulars column that is in B12 and some price e.g. 27 in C12 and hit enter
see what happens in sheet
the event code is already in the sheet code. still I am repeating that also
REMEMBER THAT THE WORD "FILES" OR OTHER PARTICULARS IF THERE IS SPELLING MISTAKE WHEN ENTERING IN SHEET 1 IT WILL GIVE A BUG. SO BE CAREFUL.
If there is a bug the code statement where the code stops and also error message if any may be given.
THE EVENT CODE IS
'----------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim part As String, price As Double, cfinddte As Range, dte As String, cfindpart As Range
If Target.Column <> 3 Then Exit Sub
price = Target.Value
part = Target.Offset(0, -1).Value
'MsgBox part
dte = Target.Offset(0, -2).Value
'MsgBox dte
With Worksheets("sheet2")
Set cfinddte = .Cells.Find(what:=dte, lookat:=xlWhole)
Set cfindpart = .Cells.Find(what:=part, lookat:=xlWhole)
Intersect(.Columns(cfinddte.Column), .Rows(cfindpart.Row)) = price
End With
End Sub
'------------------------------------
Apr 4, 2011 at 07:43 AM
Please find below the new link....
http://www.speedyshare.com/files/27771887/Ref_sheet.xls
I am a little stuck with Vlookup/match formula and i would appreciate if you could help me.
I need to subtract the value in the adjoining column which has been marked "x" from the monthly total in Worksheet 2.
Also if you could help me with a formula which would pick the monthly price value of the particulars in worksheet1 and place them in another worksheet2. For example, in worksheet1, when i type "files" and price "30.00" in Apr'10, it should pick up the price in the month of Apr'10 and place them in their respective columns in worksheet 2.
Thanks in advance for your valuable suggestions and solutions.