^ 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 :)
Forum Topic
Patulong po! regarding sa Excel..
-
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 -
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
-
thanks po ram2010.
-
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 -
^ up
-
Up
-
@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. -
^up
-
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 -
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 -
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 -
@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 -
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)
-
patulong po
-
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 -
^^
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 -
@emilbook
thanks po. -
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. -
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. -
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.