Retrieving the column data into a table based on a value in another sheet

Hi laansesu,
. - I do not think anyone can follow your explanation.
. - I think a formula would be very difficult.
. - A code solution should be possible.


. How about this offer:

. If you
. 1 ) would consider a VBA code solution
And
****. 2 ) Upload clear Before and After “Picture” or “Pictures” as well as your explanations so I can see exactly what you want.
( DO NOT POST IMAGES – Upload Files, or one File with example “Before” and “After” sheets)
And
. 3 ) No one else offers you a solution in the meantime
Then
I will try to give you a VBA code solution over the Week End
.............................................


****
. The Before or Befores, should look just as it / they does before running of any macro.
.
. The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores
. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.


. Please remember this is a Free Forum, so you need to do your best to give us clear information to help us to help you.

Alan
Germany

Hi

Originally Posted by laansesu

.....
What has to be retrieved from 1st sheet is highlighted with yellow colour in the second sheet ...

. I understand that
.......................................

Originally Posted by laansesu

..... even used reference in sheet2 reg column data of sheet1 that has to be retrieved.....

. I think I understand that...
. I beleive you mean that you used a formula to get that Info from the first sheet to make it clear from where the data is coming from. Correct ?

.............................................

. I still have no idea what you want. What do you want to happen?

. 1 ) Should sheet 2 ( "GRNS" ) be updated every time a new row is added in sheet1 ( "RECEIPTS" ) ?

. 2 ) If not, please try to explain

. 2 a) what you want to happen.

. 2 b) What should cause that to happen

Alan

Originally Posted by laansesu

......
Point 1 you mentioned is what I meant, but based on criteria i.e., supplier name (highlighted in yellow) & even has to get address 1 & address 2 also

.. I still do not understand.

. Please look at your uploaded Before ( Stock Details_Before.xlsx‎ )

. I need to know exactly
. (i) What
and
. (ii) Where
do you type something in which should result in my code giving you the output you demonstrated in your After ( Stock Details_After.xlsx‎ )

. you said ..

Originally Posted by laansesu

.......but based on criteria i.e., supplier name (highlighted in yellow) & even has to get address 1 & address 2 also

... If you require that the info highlighted in yellow in sheet GRNS is based on an input by you of a supplier name ( BGRS ) in cell D11 of sheet GRNS, then that should have been shown in your Before File thus:

Before:

Using Excel 2007

Row\Col	B	C	D	E	F	G	H	I	J
7				PURCHASE / JOBWORK					DATE
8									SOURCE
9	M.F.N./ Supplier's Challan / Bill No :		101	INVOICE DATE	30.12.2014
10	Inspection Report No:
11	Received the following from :		BGRS
12
13			Hyderabad
14	Sl.No.	Description	Unit	Quantity		Rejection		Accepted
15				Asper Challan	Actual Receipt	R.M. Rejection	Vendor Rejection		Rate Rs.P
16	1
17	2

GRNS

......

Similarly your After should have looked like this:


Using Excel 2007

Row\Col	B	C	D	E	F	G	H	I	J
7				PURCHASE / JOBWORK				DATE	17.08.2015
8								SOURCE
9	M.F.N./ Supplier's Challan / Bill No :		101	INVOICE DATE	30.12.2014
10	Inspection Report No:
11	Received the following from :		BGRS
12			Kukatpally		Surajnagar
13			Hyderabad
14	Sl.No.	Description	Unit	Quantity		Value	Discount
15				Asper Challan	Actual Receipt			Rate Rs.P	Net Amount
16	1	Rotor	1	700	500	6000	240	5760	6048
17	2	Stator	2	5000	4000	90000	4500	90000	91800
18	3
19	4

GRNS

.......

If this is not the case, please re produce carefully your Before and After to correctly reflect your requirement.!!!

Alan
P.s.
(iii) Note also: Your sheets for GRNS are have different formats in the Before and After !!!!

Originally Posted by laansesu

Dear Alan,

Whatever you shown in the picture is perfect i.e., If I type BGRS in sheet2/GRNS Sheet (as shown in your screenshot1) then its relative data has to be appeared in the below table which has been highlighted (with yellow) in your screenshot2.

I think above details makes sense for you

.. so which of the two files you sent shall I use??? As I mentioned

Originally Posted by Doc.AElstein

........
P.s.
(iii) Note also: Your sheets for GRNS are have different formats in the Before and After !!!!

......the two files had different formats so I must know to which File the code should apply. - Any Code I do for you will not work on two different Files

Hi. Laansesu,
. Ok, how about this to be going in with...

Start.... ( BEFORE )

Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
6	1120122014	1KRR01	1	20.12.2014	101	800	30.12.2014	YAP	BGRS	Kukatpally	Surajnagar	Hyderabad	1	KRR01	Rotor	500		700	0	500	12	6000		240	5760					288	600		180		6048	12.096	288
7	2130122014	1KRAS1	1	30.12.2014	101	900	30.12.2014	YAP	BGRS	Kukatpally	Surajnagar	Hyderabad	2	KRAS1	Stator	5000	0	5000	0	4000	22.5	90000		4500	90000					1800				0	91800	18.36	1800
8	1230122014	2KRAI1	2	30.12.2014	102	901	30. Dez	ACAST	Alucast	SR Nagar	Sri Nagar Colony	Hyderabad	1	KRAI1	Canopy	8000		8000	0	7000	66	462000		2000	462000					9240				0	471240	58.905	9240

RECEIPTS

Using Excel 2007

Row\Col	B	C	D	E	F	G	H	I	J
11	Received the following from :
12
13			Hyderabad
14	Sl.No.	Description	Unit	Quantity		Value	Discount
15				Asper Challan	Actual Receipt			Rate Rs.P	Net Amount
16	1
17	2
18	3
19	4
20	5
21	6

GRNS

...............................

AFTER putting BGRS in cell D11

Using Excel 2007

Row\Col	B	C	D	E	F	G	H	I	J
11	Received the following from :		BGRS
12			Kukatpally		Surajnagar
13			Hyderabad
14	Sl.No.	Description	Unit	Quantity		Value	Discount
15				Asper Challan	Actual Receipt			Rate Rs.P	Net Amount
16	1	Rotor	1	700	500	6000	240	5760	6048
17	2	Stator	2	5000	4000	90000	4500	90000	91800

GRNS

.............................................

Code to do the above in next Post, Post # 11

....

Code for Post # 10 above

Please Login or Register  to view this content.

Note:
. This is a “Worksheets Change” Program. It starts automatically when you type a new entry in cell D11
. It must be placed in the worksheet “GRNS” Module, not a Normal Macro Module.
. To do that...

. 1 ) Copy the code complete to the clipboard.
. 2 ) Right mouse click on a GRNS sheet tab
. 3 ) select an option something like “Show Code” ( I do not know exact English wording as my Excel is German )
. 4 ) You should now see the VB Development Environment Window shown.
. 5 ) Paste in the code in the large Empty code Window that.
. 6 ) Hit Alt + F11 to get out of the VB Development Window and back into “normal” Excel Spreadsheet Window
. 7 ) Resave your file, but select the ( usually second ) option of something like “Excel Workbook with Macros”
. 8 ) That is it. The code should then always start when you type an entry in cell D11

..........................................
. Please let me know how you get on.
Alan

Hi laansesu......

Originally Posted by laansesu

.....
When I started typing details in Receipts sheet cells I'm getting pop-up msg that "That was not an entry in D11". When I completed typing all details in receipts sheet ignring the above msg, I'm going to GRNS sheet and entered the supplier name in D11. I couldnt see any data being appeared in the table.......

... It sounds like you copied the Code to the wrong sheet Module, - It sounds like you copied the Code into The RECEIPTS Module. It should be copied to the GRNS Sheet module....

Originally Posted by Doc.AElstein

........
Note:
. This is a “Worksheets Change” Program. It starts automatically when you type a new entry in cell D11
. It must be placed in the worksheet “GRNS” Module, not a Normal Macro Module.
. To do that...

. 1 ) Copy the code complete to the clipboard.
. 2 ) Right mouse click on a GRNS sheet tab
. 3 ) select an option something like “Show Code” ( I do not know exact English wording as my Excel is German )
. 4 ) You should now see the VB Development Environment Window shown.
. 5 ) Paste in the code in the large Empty code Window that.
. 6 ) Hit Alt + F11 to get out of the VB Development Window and back into “normal” Excel Spreadsheet Window
. 7 ) Resave your file, but select the ( usually second ) option of something like “Excel Workbook with Macros”
. 8 ) That is it. The code should then always start when you type an entry in cell D11
.........

Alan

Originally Posted by laansesu

Really Very Sorry for my mistake..even now getting the same message after pasting the same code in GRNS Sheet code (nor) getting the data from receipts sheet into the table

Please clarrify this and do the needful.

. Have you also deleted the code from the RECEIPTS Worksheet module?

. The code must only be in GRNS Worksheet module.
. There must be no code in the RECEIPTS Worksheet module

Originally Posted by laansesu

Yes, I've deleted the code in GRNS Work Sheet Module & I'm now getting this message when I entered the supplier name in GRNS Sheet

??? The code must be in the GRNS Module .
.You must have no code in RECEIPTS Worksheet Module

Originally Posted by laansesu

I dont know whats confusing me?? Really very sorry I've deleted in receipts work sheet module not in GRNS module.

Now, I've code in GRNS Module & deleted code in Receipts module.

?? So is it now working??

. Are you still haveing problems?

Originally Posted by laansesu

Its not working.
......

Hi
It is still working by me!
It is difficult to guess at this distance what the problem may currently be.
.. If you wish, I can take a look at your file......So send me it to me

. If you wish to send the file to me: You have 3 ways to do this:

To Post Files

. 1) To Attach a sample workbook: ( As you did before in Post # 4 - So you already know how to do that)
View Pic
http://www.excelforum.com/excel-prog...ch-a-file.html
http://tinyurl.com/oenwprw

. 2) Send over a file sharing site, such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give.

. 3) Only as a last resort, P.M **( Private Message ) . me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Alan