true power of MS excel. update: PowerBI

[aloidydnor]

Re: MS excel we give solutions. formula, codes and tutorials.

salamat tol..nice thread

 

[acelleb]

Re: MS excel we give solutions. formula, codes and tutorials.

okay lang sir kirby21, mas maganda na din kung naka macro sya. salamat in advance.



seen this, one follow up question sir. paano ko sya e timestamp on that specific cell, since every choice that i make ay ilalagay sa specific cell.

Go to FILE --> Option --> Formula --> Workbook calculate to Manual, uncheck mo din recalculate when saving.

Then sa worksheet name, right click then view code.

add mo to

Private Sub Worksheet_Change(ByVal Target As Range)

R = ActiveCell.Row
Range("A" & R & ":C" & R).Calculate

End Sub


A hangan C ginamit ko, kasi sa sample mo po column A ung Issued date at C nmn c Cancelled Date.

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

Go to FILE --> Option --> Formula --> Workbook calculate to Manual, uncheck mo din recalculate when saving.

Then sa worksheet name, right click then view code.

add mo to

Private Sub Worksheet_Change(ByVal Target As Range)

R = ActiveCell.Row
Range("A" & R & ":C" & R).Calculate

End Sub


A hangan C ginamit ko, kasi sa sample mo po column A ung Issued date at C nmn c Cancelled Date.

tama. galing mo. gawin manual ang calculation ang formula. di ko agad naisip para di na kailangan ipaste as values using macro.

 

[th3j3st3r]

Re: MS excel we give solutions. formula, codes and tutorials.

thanks sa pagturo mga bossing,

so far, nainput ko naman yung binigay na instructions but sadly ayaw niya magstamp. may attached file ako as to what i wanted the condition to happen.

1. If Status equals "Issued" display current date and time else "Uncollected" display current date and time in cell Remarks.
2. If Status equals "Cancelled" display current date and time.

Sa attached ko po na photo, pansin niyo is magkaiba ang dates this is because may times kasi na nagsusubmit ng permit ang isang supervisor pero hindi naman naiissue kaagad, so in this case the permit will be automatically marked as Uncollected. As soon as the supervisor will collect the said permit then that's the time it will be marked as Issued.

Problem ko po is, once naglagay ako ng status as Issued, Cancelled or Uncollected ang nakatatak na date sa isang cell ay mawawala and it will be reflected on the cell to which the status is related.

Sana naipaliwanag ko ng maayos. hehe.. sensya na mga bossing ha. balik aral kasi ulit ng programming.

patulong po ulit

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

Sir Follow Up ko lang po yung Help ko sana matulungan nyo po ako

 

[rael01]

Re: MS excel reportings, presentations, and vba helping thre

sir baka po meron kayu gawa na access jan need lang sa subdivision nmn para sa monthly dues
-can calculate monthly dues
-can add another home owners record
-can add and delete record
-must have ah login user and passord
-can pop up a homeowners details of payment from the first pay.
-can add pictures.
-registration form must have the folloowing.

-name
-last name
-age
-contact number
-block
-lot
-street
-BRGY.

salamat po sa makakapagbigay hahah ^_^
[email protected]
-https://www.facebook.com/israepagay


message na lang po salamat. po

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

Sir pa help nmn po need na need ko ung sagot nito wla tlga akong makitang solution sa net

Eto po ung ask ko
hindi nya po na count ung same date of start date and end date po pano po ba sia nakakasama sa result

angnabibilang nya lng po eh ung 3/3-3/5 po dapat pati ung 3/2 nakakasama

ang nagiging result po kase
=is 1.5
dapat po 1.75

eto po ung code ko

=IF(SUMIFS('Applicants Tracking_2015'!BS:BS,'Applicants Tracking_2015'!I:I,$C$2,'Applicants Tracking_2015'!K:K,$D$2,'Applicants Tracking_2015'!G:G,"( Audit ) Secretary",'Applicants Tracking_2015'!J:J,"Passed")>0,SUMIFS('Applicants Tracking_2015'!BS:BS,'Applicants Tracking_2015'!I:I,$C$2,'Applicants Tracking_2015'!K:K,$D$2,'Applicants Tracking_2015'!G:G,"( Audit ) Secretary",'Applicants Tracking_2015'!J:J,"Passed")/COUNTIFS('Applicants Tracking_2015'!I:I,$C$2,'Applicants Tracking_2015'!K:K,$D$2,'Applicants Tracking_2015'!G:G,"( Audit ) Secretary",'Applicants Tracking_2015'!J:J,"Passed"),"0")

eto po ung data koView attachment 1014666




Boss dito na lng po ako magpapasalamat regarding sa graphic control po na tutorial big help po sa akin
hindi ako makapag post eh d ko alam kung bakit nag error dun sa ask ng system kpag sinasagot ko laging mali daw

boss di ko sinolve yung problem mo pero. check mo tong attachment ko, magkakaron ka ng idea.

 

[acelleb]

Re: MS excel we give solutions. formula, codes and tutorials.

thanks sa pagturo mga bossing,

so far, nainput ko naman yung binigay na instructions but sadly ayaw niya magstamp. may attached file ako as to what i wanted the condition to happen.

1. If Status equals "Issued" display current date and time else "Uncollected" display current date and time in cell Remarks.
2. If Status equals "Cancelled" display current date and time.

Sa attached ko po na photo, pansin niyo is magkaiba ang dates this is because may times kasi na nagsusubmit ng permit ang isang supervisor pero hindi naman naiissue kaagad, so in this case the permit will be automatically marked as Uncollected. As soon as the supervisor will collect the said permit then that's the time it will be marked as Issued.

Problem ko po is, once naglagay ako ng status as Issued, Cancelled or Uncollected ang nakatatak na date sa isang cell ay mawawala and it will be reflected on the cell to which the status is related.

Sana naipaliwanag ko ng maayos. hehe.. sensya na mga bossing ha. balik aral kasi ulit ng programming.

patulong po ulit

Alisin mo na lahat ng formula sa mga cell eto lang add mo sa code ng sheet1

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Application.Intersect(Me.Range("A2:E1000"), Target) Is Nothing Then
R = ActiveCell.Row

If Range("D" & R).Value = "Issued" Then
Range("A" & R).Value = Now
ElseIf Range("D" & R).Value = "Cancelled" Then
Range("C" & R).Value = Now
ElseIf Range("D" & R).Value = "Uncollected" Then
Range("E" & R).Value = Now
End If

End If


Application.EnableEvents = True

End Sub

 

[misaccount]

Re: MS excel we give solutions. formula, codes and tutorials.

salamat ng maraming marami dito tsong

 

[007heran]

Re: MS excel we give solutions. formula, codes and tutorials.

Nice TS... May ask lang ako about sa simple excel na ginagawa ko po sa procurement form. La po bang simple way na pag na-total mo na ung all items cost ay automatic fill naman yung amount in words sa adjacent cell nung total? Senxa, la akong background maxado sa excel kaya kakapakapa pa lang ako... Thanks ng marami bossing!

 

[jmedina]

Re: MS excel we give solutions. formula, codes and tutorials.

Good Afternoon sir.

Sir pa-post din po about sa openoffice calc about programing using toggle button add and delete cell. and other useful codes

thank you sir.

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

Nice TS... May ask lang ako about sa simple excel na ginagawa ko po sa procurement form. La po bang simple way na pag na-total mo na ung all items cost ay automatic fill naman yung amount in words sa adjacent cell nung total? Senxa, la akong background maxado sa excel kaya kakapakapa pa lang ako... Thanks ng marami bossing!

pwede makita ng gawa mo boss?

- - - Updated - - -

Good Afternoon sir.

Sir pa-post din po about sa openoffice calc about programing using toggle button add and delete cell. and other useful codes

thank you sir.

bakit di nalang po ms excel instead of open office?

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

Boss kirby21 thx u po sa idea.rar sinilip ko sia ATM pero nagawan ko na po sia ng paraan may pinapagawa na naman po sa akin report nmn po
ang hindi ko makuha eh ung text outpot po na mag search in multi data

sample po
meron akong 10 applicant isa dun ang na hire gusto ko po makuha ung taong un pano po ba makkukuha pero ang key ko ay ung kelan sia mag sisimula pumasok un po hirap nya i display or ilabas sa column ginawa ko kase po if statement pero ang if statement kase per column sia kaya d na filter ung gusto kong applicant na makuha pde po bang patulong po ulit ako thx u po advance

eto po samle code ko
=IF(B1='Applicants Tracking_2015'!BG:BG,"F",IF(Sheet4!B2='Applicants Tracking_2015'!G:G,"F",IF(Sheet4!B3='Applicants Tracking_2015'!BF:BF,"F",'Applicants Tracking_2015'!E:E)))

View attachment 208825

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

Boss kirby21 thx u po sa idea.rar sinilip ko sia ATM pero nagawan ko na po sia ng paraan may pinapagawa na naman po sa akin report nmn po
ang hindi ko makuha eh ung text outpot po na mag search in multi data

sample po
meron akong 10 applicant isa dun ang na hire gusto ko po makuha ung taong un pano po ba makkukuha pero ang key ko ay ung kelan sia mag sisimula pumasok un po hirap nya i display or ilabas sa column ginawa ko kase po if statement pero ang if statement kase per column sia kaya d na filter ung gusto kong applicant na makuha pde po bang patulong po ulit ako thx u po advance

eto po samle code ko
=IF(B1='Applicants Tracking_2015'!BG:BG,"F",IF(Sheet4!B2='Applicants Tracking_2015'!G:G,"F",IF(Sheet4!B3='Applicants Tracking_2015'!BF:BF,"F",'Applicants Tracking_2015'!E:E)))

View attachment 1018203

san mo nilagay yung formula? at pwede pascreen shot din ng data mo? yung mga column

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

san mo nilagay yung formula? at pwede pascreen shot din ng data mo? yung mga column

dun po sa pangalan andun po ung code

 

[jeberns12]

Re: MS excel reportings, presentations, and vba helping thre

Can you help me with this sir? I need a formula for my task. nasa billing dept. kasi ako. May e fo-forward akong certain amounts sa may collection, tapos indi lahat ma deduct nila.

Ang gusto ko po sir is pagdating mismo sa amount na binigay nila, ma distribute sya automatically then mauuna ang nasa interest na part.

Halimbawa: P5,000 na eforward ko sa collection. Pag balik nila, P4000 lang ang na deduct kasi di kaya sa sweldo ng client. Sa distribution ng 5K ko is:

Cash Advance: Principal: 3500 then Interest: 500
Share: 500
Mortuary Fund: 500

Kaya lang P4,000 lang bali na deduct sa sweldo ng client. So gusto ko pag input ko sa amount, automatic nya ma distribute yung payment na P4,000 sa CA, Share and MF. Pwede ba yun sir? bali mauuna ang MF, saka Interest tapos principal ng loan. Pwede ba yun sir?

 

[nash0260]

Re: MS excel reportings, presentations, and vba helping thre

Can you help me with this sir? I need a formula for my task. nasa billing dept. kasi ako. May e fo-forward akong certain amounts sa may collection, tapos indi lahat ma deduct nila.

Ang gusto ko po sir is pagdating mismo sa amount na binigay nila, ma distribute sya automatically then mauuna ang nasa interest na part.

Halimbawa: P5,000 na eforward ko sa collection. Pag balik nila, P4000 lang ang na deduct kasi di kaya sa sweldo ng client. Sa distribution ng 5K ko is:

Cash Advance: Principal: 3500 then Interest: 500
Share: 500
Mortuary Fund: 500

Kaya lang P4,000 lang bali na deduct sa sweldo ng client. So gusto ko pag input ko sa amount, automatic nya ma distribute yung payment na P4,000 sa CA, Share and MF. Pwede ba yun sir? bali mauuna ang MF, saka Interest tapos principal ng loan. Pwede ba yun sir?

prang magulo ata ung gusto mo mangyari pde po paki explain ng maige gusto ko makatulong din sau
ang na gets ko sa paliwanag mo eh ung bang suweldo ng client mo eh magiging 1k n lang tama ba?
ung 4k eh ibabayad sa CA ng 3500 at sa MF na 500 tama ba?

 

[kirby21]

Re: MS excel we give solutions. formula, codes and tutorials.

dun po sa pangalan andun po ung code

di ba pwede vlookup? gawa k nmn sample data.

- - - Updated - - -

Can you help me with this sir? I need a formula for my task. nasa billing dept. kasi ako. May e fo-forward akong certain amounts sa may collection, tapos indi lahat ma deduct nila.

Ang gusto ko po sir is pagdating mismo sa amount na binigay nila, ma distribute sya automatically then mauuna ang nasa interest na part.

Halimbawa: P5,000 na eforward ko sa collection. Pag balik nila, P4000 lang ang na deduct kasi di kaya sa sweldo ng client. Sa distribution ng 5K ko is:

Cash Advance: Principal: 3500 then Interest: 500
Share: 500
Mortuary Fund: 500

Kaya lang P4,000 lang bali na deduct sa sweldo ng client. So gusto ko pag input ko sa amount, automatic nya ma distribute yung payment na P4,000 sa CA, Share and MF. Pwede ba yun sir? bali mauuna ang MF, saka Interest tapos principal ng loan. Pwede ba yun sir?

just to give you idea boss ha. ganto gawin mo sum mo muna yung mga deductions. katulad ng sinabi mo kulang yung collection ni client

so =if(deductionsum>=collection,(if(deductionsum-principalloan)>=collection,if((deductionsum-principalloan)-interest)>=collection,if etc etc etc . so bawas ka ng bawas ng deduction hanggang magfit sya sa sahod.

 

[nash0260]

Re: MS excel we give solutions. formula, codes and tutorials.

Yes Boss vlookup na ung gamit ko ang problema lang po sa vlookup eh dapt ung date hire lagi nasa columnA may kya nag create ako ng duplicate for that mern po bang paraan na pde gamitin ang vlookup na hindi na nag create or nag duplicate ng data ?


Sir pwede po bang pa help pano gumamit ng macro na ang coding do while with vlookup kase may gusto ko pong palabasin lahat ng result hindi lang po isa

 

[pclogger]

Re: MS excel we give solutions. formula, codes and tutorials.

TS pa help,
situation: gusto ko e copy ung data sa sheet1 papunta sa sheet 2, tapos kada copy magkakaroon ng 1 blank row sa pinakababa at after ng blank row dun naman macocopy ang next data na nasa sheet1 (Reminder: ung sheet1 ko 1 lng ung data exmple: apple after ko macopy s sheet 2 using macro mag iinput ulit ako ng new data which is banana, at mapupunta siya sa baba ng apple, pero my space between them)