Subtract the value in the adjoining column

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

Thank you for your response. I'm sorry about the link, i must have copied it wrong.

Please find below the correct link....

https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://docs.google.com/spreadsheets/&followup=https://docs.google.com/spreadsheets/<mpl=sheets

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.

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
the url of your uploaded file is something wrong. it should contains the name of the file with extensions after .xom/
Thank you for your advice.

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.
Posts
6428
Registration date
Sunday June 6, 2010
Status
Moderator
Last seen
July 16, 2020

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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
'------------------------------------