-
Chill_at_tpc
on
09 May 12 @ 06:45 PM #
^ It worked! Thanks ram2010 this is exactly what i need, but I may come back if I have difficult questions :p
+100 for you sir :)
-
pixelspics
on
10 Feb 14 @ 09:40 AM #
patulong po ulit sa nakakaalam. gusto ko idrag yung excel formula na ganito
kalalabasan as eblow example. panu po gawin sa excel.
example:
=G4
=H4
=I4
=G5
=H5
=I5
=G6
=H6
=I6
-
ram2010
on
11 Feb 14 @ 01:11 PM #
pixelspics Send Message View User Items on February 10, 2014 09:40 AM
patulong po ulit sa nakakaalam. gusto ko idrag yung excel formula na ganito
kalalabasan as eblow example. panu po gawin sa excel.
example:
=G4
=H4
=I4
=G5
=H5
=I5
=G6
=H6
=I6
You did not specify where \"=G4\" will start, but anyway here is a sample solution. It uses the offset() as main formula and manipulating the rows and columns with
if statement.
Write the formula below in cell A9.
=OFFSET(IF(ROW()>=15,$F$5,IF(ROW()>=12,$F$4,$F$3)), COLUMN(),IF(ROW()>=15,ROW(A1)-6, IF(ROW()>=12,ROW(A1)-3,ROW(A1))))
Then drag till A17 to get what you wanted.
To debug, write:
5 in cell G4
4 in cell H4
3 in cell I4
6 in cell G5
3 in cell H5
1 in cell I5
11 in cell G6
9 in cell H6
7 in cell I6
What you will see after dragging the formula will be:
a9 = 5 till a17 = 7
5
4
3
6
3
1
11
9
7
-
pixelspics
on
02 Mar 14 @ 06:03 AM #
thanks po ram2010.
-
datingzubenscii
on
15 Dec 14 @ 12:44 PM #
Excel Guru, patulong po se exercise nato.
may two Excel Sheets more than 1000 records at hindi na advisable isa-isahin.
Sheet 1 is Client Records:Name, Address, Contact Details, required Stock
Sheet 2 is Stock Details: Stock Name, Type, Price, Location
Task is: my Drop Down List about Client Name. If i select on a Client Name iddisplay nya sa next Raw yung Lahat ng Client Record
at sa next Raw, iddisplay naman nya un Stock Detail base dun sa Required Stock from Client Record Sheet.
Share naman po mga Guru ng sample panu ko ma complete ang exercise nato.. Maraming Salamat po In Advance
-
datingzubenscii
on
15 Dec 14 @ 01:37 PM #
^ up
-
datingzubenscii
on
15 Dec 14 @ 02:25 PM #
Up
-
skabuts
on
15 Dec 14 @ 03:10 PM #
@dating
you may try this,
assume this is your workbook,
sheet a - client records
a b c d
1 name add con req
2 a 1 4 d
3 b 2 5 e
4 c 3 6 f
sheet b - stock details
a b c d e
1 sd sn t p l
2 d g j 7 m
3 e h k 8 n
4 f i l 9 o
sheet c - client list
a b c d e f g h i aa
1 name add con req sd sn t p l
2
Client List Steps
1. sheet c, copy all the details from sheet a cell a2 to a end.
2. sheet c, use data validation on cell a2, use list and use cell aa1 to aa end (the one you copied from sheet a)
3. sheet c, cell b2 type =LOOKUP($A2,a!$A$2:$A$4,a!$B$2:$B$4)
4. sheet c, cell c2 type =LOOKUP($A2,a!$A$2:$A$4,a!$C$2:$C$4)
5. sheet c, cell d2 type =LOOKUP($A2,a!$A$2:$A$4,a!$D$2:$D$4)
6. sheet c, cell e2 type =LOOKUP($D2,b!$A$2:$A$4,b!$A$2:$A$4)
7. sheet c, cell f2 type =LOOKUP($D2,b!$A$2:$A$4,b!$B$2:$B$4)
8. sheet c, cell g2 type =LOOKUP($D2,b!$A$2:$A$4,b!$C$2:$C$4)
9. sheet c, cell h2 type =LOOKUP($D2,b!$A$2:$A$4,b!$D$2:$D$4)
10. sheet c, cell i2 type =LOOKUP($D2,b!$A$2:$A$4,b!$E$2:$E$4)
11. just pick from the dropdown list in a2
12. copy then paste or drag cell a2-i2 downwards if you want to duplicate the list
by the way, i used multiple columns pari iwas clutter, pag kasi isang column lang baka maging magulo yung details pag pinagsama sama.
-
datingzubenscii
on
15 Dec 14 @ 03:11 PM #
^up
-
ram2010
on
15 Dec 14 @ 04:05 PM #
datingzubenscii Send Message View User Items on December 15, 2014 12:44 PM
One way of doing this is by using pivot table.
Sample table.
Pivot table displaying only the two clients. Display can be controlled, quantities can be summarized.
If data expands, pivot table can be updated.
-- edited by ram2010 on Dec 15 2014, 04:08 PM
-
datingzubenscii
on
15 Dec 14 @ 07:38 PM #
Ayun.. Na-gets ko na..haha..maraming salamat po mga Guru.
@ram2010 - Sir madaming salamat sa dagdag kaalaman.
@skabuts - Sir madaming salamat po sa medyo Spoon feed nako sa binigay mo.hehehe.
Panu naman po ba ang pag gamit ng VB Editor dito sa Excel?
Salamat po ulit
-
ram2010
on
16 Dec 14 @ 02:24 PM #
datingzubenscii Send Message View User Items on December 15, 2014 07:38 PM.
Panu naman po ba ang pag gamit ng VB Editor dito sa Excel?
Salamat po ulit
In excel press ALT+F11, press insert module, then you can write say your sub function.
Press the green triangle pointing right to run the function, and the result would be.
-- edited by ram2010 on Dec 16 2014, 02:25 PM
-
skabuts
on
16 Dec 14 @ 02:59 PM #
@dating
yung sa vba, kadalasan gamit yan pang macros, nagamit namen dati yan ng nag automate kame ng digits to words, madae pang gamit yan, sa google nga lang ako kumukuha ng mga tinatype kasi wala akong alam sa vba, haha.
yung sa tut, medyo magulo yung nasabe ko kasi di ko alam spacing dito, pero kahit papano naman makukuha na yan basta naka plot sa excel saka inimagine mo pinagsasabe ko kahit malabo saka magulo, haha.
-- edited by skabuts on Dec 16 2014, 03:03 PM
-
z3phyr2012
on
30 Mar 15 @ 10:39 AM #
mga bossing, pa help naman po. pano po gagawin ko sa insert function? ung sa baba na result po ang gusto ko makuha.
thanks a lot! (y)
-
Zendrix
on
30 Mar 15 @ 10:55 AM #
Baka po pwede request na lipat tayo dito.
<click here for link>
-
PrestO
on
24 Feb 17 @ 11:23 AM #
patulong po
-
pixelspics
on
29 Sep 21 @ 04:56 PM #
Patulong sa excel file. Panu po transfrom yung table sa excel file as below
*********** original table *******************
Column A Column B
A 2
A 3
B 5
C 6
C 7
************** Transform to below ****************************
Column A Column B Column C
A 2 3
B 5
C 6 7
Bale yung mga numbers nasa Column B and Column C. Di lang maayus pagkakaadjust ng tipidpc.
thanks.
-- edited by pixelspics on Sep 29 2021, 01:58 AM
-
emilbook
on
29 Sep 21 @ 06:54 PM #
^^
Kung baga yung First Column A will dictate kung saan ilalagay yung number?
This can be done without VBA
Use "ctrl-shift+enter" to input the formula. Enter only for single cell, then just drag from left to right. Change the If="x" depending sa Column A.
-- edited by emilbook on Sep 29 2021, 04:04 AM
-
pixelspics
on
02 Oct 21 @ 07:52 PM #
@emilbook
thanks po.
-
Laserion
on
04 Oct 21 @ 07:14 PM #
Excel ko ay for creating list lang. Basic knowledge na parang pang elementary...
Sa Youtube balak mag search ng mga Excel Tutorials...
Sa real-world practice ng mga company or small business ano ba dapat malaman ko sa Excel? Ano ba typical ginagawa sa excel sa mga office... creating chart, graph, inventory, formula sa pag compute....
Maraming Salamat sa mabait na sasagot sa tanong.
-
iceman16
on
05 Oct 21 @ 01:27 PM #
Sa real-world practice ng mga company or small business ano ba dapat malaman ko sa Excel? Ano ba typical ginagawa sa excel sa mga office... creating chart, graph, inventory, formula sa pag compute....
depende sa field mo sa company. if you're in IT, you will extract records from different sources -- SQL, SAP, internal reports, etc.
then you need to learn vlookup and pivot functions. also understand how to analyze and present your analysis thru pivot.
for more complex vlookup, next step is to learn index-match combo.
then depende din sa kelangan mo, you will need to learn nested functions. usually with functions like ifs, and, or, textjoin, etc.
-
Laserion
on
06 Oct 21 @ 06:24 PM #
depende sa field mo sa company. if you're in IT, you will extract records from different sources -- SQL, SAP, internal reports, etc.
then you need to learn vlookup and pivot functions. also understand how to analyze and present your analysis thru pivot.
for more complex vlookup, next step is to learn index-match combo.
then depende din sa kelangan mo, you will need to learn nested functions. usually with functions like ifs, and, or, textjoin, etc.
Noted. Thanks, bro! will do research those things. I'm not quite familiar with those terminology yet.