• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can any one help me with this work sheet??

Dhanushr

Dhanushr

New Member
https://docs.google.com/file/d/0BwX4UcNT9198S2hOWVljcUhONk0/edit?usp=sharing


I’m working on purchase and issue of materials for a construction company and this is what I want,

1. when I insert a particular value [e.g. 1] in particular cell [e.g. K7] in Sheet2, I want the entire data in that row [Sheet 2, row7] to automatically appear in a particular row in Sheet 3 and again when the same value is used again in another cell [same column different row] of sheet 2, that data should come in the next row of sheet 3. Now is this possible??

2. in sheet 2 I will be entering lots of materials as they come and when they are issued. That is the same item will be repeated a lot. So in last sheet I want all the items in sheet 2 to appear only once its totals in the next column is that possible? Please help
 
This topic is the same as last question of your only other one, just a day of difference:

http://chandoo.org/forums/topic/how-to-get-data-from-one-sheet-to-another


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the main green sticky post at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker.

If you do cross post, please put that in your post.

Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."
 
Hi, Dhanushr!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Can%20any%20one%20help%20me%20with%20this%20work%20sheet__%20-%20Stock_Register_Dhanush%20%28for%20Dhanushr%20at%20chandoo.org%29.xlsm


I did this:

a) Filled a new column N in worksheet Index to get the actual name of each worksheet.

b) Placed a formula in each P1 cell of unit worksheets to retrieve the name of the worksheet as filled in a).

=Index!Nxx

c) Added a worksheet "Apt 12" that was missing.

d) Created a new column P for table in worksheet Dhanush.

e) Created 2 named ranges in worksheet Dhanush (AptNoList and AptNoOrderList for columns K & P of worksheet Dhanush.

f) Converted to ranges tables of worksheets "Apt 1" & "Apt 2" for testing and checking if that'd be suitable for you. This is a requirement since tables can't handle array formulas.

g) Placed this array formula in each cell of range tables of worksheets "Apt 1" & "Apt 2":

=SI.ERROR(SI(INDICE(TableDhanush;COINCIDIR($P$1&"_"&FILA()-5;AptNoList&"_"&AptNoOrderList;0);COLUMNA())="";"";INDICE(TableDhanush;COINCIDIR($P$1&"_"&FILA()-5;AptNoList&"_"&AptNoOrderList;0);COLUMNA()));"") -----> in english: =IFERROR(IF(INDEX(TableDhanush,MATCH($P$1&"_"&ROW()-5,AptNoList&"_"&AptNoOrderList,0),COLUMN())="","",INDEX(TableDhanush,MATCH($P$1&"_"&ROW()-5,AptNoList&"_"&AptNoOrderList,0),COLUMN())),"")

Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


If you agree with this solution, all the unit worksheet tables should be converted to range tables to handle the array formula, and it'd remain the issue of totalizing items in last worksheet.


Check it and confirm if it's a valid solution.


Regards!
 
Thank you thank you a million times..... This will help me in Soooo many levels!!! Sir JB I am sooooo sorry i broke some of the rules. but i am sooo glad that you helped me. now i am jumping like nuts.... thank you..
 
Hi... Thanks for all the help. I didn't forgot "Apt 12". Its because there is no "Apt12"!. This particular project is a 11 apartment storied building!


How do i copy the formula to other sheets? i did't understand the last part of your post!!


"""Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


If you agree with this solution, all the unit worksheet tables should be converted to range tables to handle the array formula, and it'd remain the issue of totalizing items in last worksheet."""
 
Hi, Dhanushr!


If there isn't an "Apt 12" then the Index worksheet is wrong. So either it should exists an "Apt 12" or there's an invalid entry in the 1st worksheet.


For replicating "Apt 1" & "Apt 2" solution to all other unit worksheets you must do this for each of these worksheets:

a) Select the worksheet.

b) Select any cell within the table.

c) Click on the floating Table Tools menu.

d) Tools group (2nd), click on Convert To Range.

e) Select 1st row of range table from "Apt 1" worksheet (A6:O6), copy.

f) Select 1st column of range table from unit worksheet ("Apt 3/12", "Ground...", ...) (A6:A210), paste


This will solve the distribution issue from worksheet Dhanush, but remember that in this worksheet you should enter "Apt 1" and not 1 as you intended, that's to say, the actual name of each unit worksheet and entry in column N of worksheet Index.


Once done that, upload your updated file and me or someone else maybe helps you with the summary totalizing last worksheet.


Regards!


PS: If you follow carefully the described steps you shouldn't worry about array formulas and Ctrl-Shift-Enter procedure; that's for individual cells when you enter or edit a new formula, not when copying already existing ones.
 
Hi there ... I have updated the sheet and the link is below. If the last sheet also worked it would be a Blast. There are only 14 floors in this building. Ground & 1st floor will be parking, 2nd floor would be used as common area with pool health club etc, Apartments will start from Floor 3, there will be only 11 nos [Floor 3= Apt1, floor 4=Apt2,5=3, 6=4, 7=5,8=6, 9=7, 10=8, 11=9, 12=10 & 13=11] 14th floor is the terrace. I might have confused you when i put Common area & Apt Common Area & other work. Common area means floor 2 with health club n other stuff while Apt Common area means the common area in each apartment floor!!! sorry for the confusion...


Here is the link pleas try to fix the last sheet to.


ie: all the Items in sheet 2 [Dhanush] to appear only once and its totals in the next coloums


https://docs.google.com/file/d/0BwX4UcNT9198SHVLWmgxX0Zxckk/edit?usp=sharing


Very sorry for the long gaps between tech post!! Its raining here and my net connection keeps breaking ....
 
Hi, Dhanushr!


Good job, all other unit worksheets were ok.


I added a few other named ranges just for making formulas easier, all of worksheet Dhanush:

ItemList

ItemCountList

OpeningList

PurchaseList

OtherList

IssueList

ClosingList


Which I don't know if they'l be used or not (see next paragraph). I also added a new column Q for table in that worksheet.


Regarding F column of last worksheet "Item Total" I don't know how to calculate it, if just the difference between columns D Purchased and E Issued or if I have to consider Opening, Others & Closing of worksheet Dhanush, so specify how to calculate all 3 columns D:F if the actual formula isn't what you intended to have.


Download again the updated file from same previous link.


Regards!
 
Hi


Column F is Just the difference of D & E [D-E], Column D will be the total of G+H+I of the 2nd sheet [Dhanush]and Column E of last work sheet will be 'J of Dhanush'. That Guess is the easy part.


The tricky part will be


[1] Column C, because It should show an item [eg Cement] only once

[2] Column D, all G+H+I of Sheet corresponding to the item in Column C

[3] Column E, should have the value in column J of sheet 2 corresponding to that item!


#I downloaded from both links but i didn't get the up dates u mentioned!
 
Hi, Dhanushr!

Yes, I'm getting noticed about it. Last version is missing, so I'll have to rebuild it :(

Regards!
 
Hi.. Thank you soo much for your help. I'm making some more modifications [beautification ]on it. I'll upload the finished work sheet soon... Thank you
 
Hi, Dhanushr!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Don't forget to build apt. 12 and extend property title to my name :=)
 
You must log in or register to reply here.
Back
Top