Transfer the data from different excel sheet to one

[allrounder]

I am preparing a price sheet where I want to create an order selection sheet.
I have multiple sheets which have some items and I want to perform selection in each sheet and transfer selected items to my order selection sheet.

 

[Toadstool]

Hi Allrounder,

You don't say how many price sheets there are or which columns are used but here's an example using 3 price sheets and a sample order sheet. You'll need to specify the sheet names in Order I2 to I99 as Excel functions can't retrieve them (NOTE: you could hide the Order columns I, J, K in a work sheet if preferred).

Allrounder.xlsx
	A	B	C
5	Cable Set 1	15 - 8185	1
6	Cable Set 2	18 - 8222	2
7	Cable Set 3	21 - 8259	3
8	Cable Set 4	24 - 8296	4
9	Cable Set 5	27 - 8333
10	Cable Set 6	30 - 8370
Price 1

Allrounder.xlsx
	A	B	C
5	Cable Set 1	15 - 8182
6	Cable Set 2	18 - 8219
7	Cable Set 44	21 - 8256
8	Cable Set 45	24 - 8293	4
9	Cable Set 46	27 - 8330	5
10	Cable Set 47	30 - 8367	6
Price 2

Allrounder.xlsx
	A	B	C
5	Cable Set 42	15 - 86845
6	Cable Set 43	18 - 88214
7	Cable Set 44	21 - 89583	3
8	Cable Set 45	24 - 810952	4
9	Cable Set 46	27 - 812321
10	Cable Set 47	30 - 813690
Price 3

Allrounder.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1									Sheets	Items	Running
2									Price 1	4	0
3									Price 2	3	4
4	Cable Sets								Price 3	2	7
5	Cable Set 1	15 - 8185	1
6	Cable Set 2	18 - 8222	2
7	Cable Set 3	21 - 8259	3
8	Cable Set 4	24 - 8296	4
9	Cable Set 45	24 - 8293	4
10	Cable Set 46	27 - 8330	5
11	Cable Set 47	30 - 8367	6
12	Cable Set 44	21 - 89583	3
13	Cable Set 45	24 - 810952	4
14
15
Order

Cell Formulas
Range	Formula
J2:J15	J2	=IF(I2="","",COUNTIF(INDIRECT("'"&I2&"'!$C$5:$C$99"),">0"))
K2:K15	K2	=IF(I2="","",SUM($J$1:$J1))
A5:C15	A5	=IF(ROW()-ROW($A$4)>SUM($J$2:$J$99),"",INDEX(INDIRECT("'"&INDEX($I$2:$I$99,MATCH(ROW()-ROW($A$4)-1,$K$2:$K$99,1))&"'!$A$5:$c$99"),AGGREGATE(15,6,INDIRECT("'"&INDEX($I$2:$I$99,MATCH(ROW()-ROW($A$4)-1,$K$2:$K$99,1))&"'!$C$5:$C$99"),(ROW()-ROW($A$4))-(INDEX($K$2:$K$99,MATCH(ROW()-ROW($A$4)-1,$K$2:$K$99,1)))),COLUMN()))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Transfer the data from different excel sheet to one
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[allrounder]

Hi Allrounder,

You don't say how many price sheets there are or which columns are used but here's an example using 3 price sheets and a sample order sheet. You'll need to specify the sheet names in Order I2 to I99 as Excel functions can't retrieve them (NOTE: you could hide the Order columns I, J, K in a work sheet if preferred).

Allrounder.xlsx
	A	B	C
5	Cable Set 1	15 - 8185	1
6	Cable Set 2	18 - 8222	2
7	Cable Set 3	21 - 8259	3
8	Cable Set 4	24 - 8296	4
9	Cable Set 5	27 - 8333
10	Cable Set 6	30 - 8370
Price 1

Allrounder.xlsx
	A	B	C
5	Cable Set 1	15 - 8182
6	Cable Set 2	18 - 8219
7	Cable Set 44	21 - 8256
8	Cable Set 45	24 - 8293	4
9	Cable Set 46	27 - 8330	5
10	Cable Set 47	30 - 8367	6
Price 2

Allrounder.xlsx
	A	B	C
5	Cable Set 42	15 - 86845
6	Cable Set 43	18 - 88214
7	Cable Set 44	21 - 89583	3
8	Cable Set 45	24 - 810952	4
9	Cable Set 46	27 - 812321
10	Cable Set 47	30 - 813690
Price 3

Allrounder.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1									Sheets	Items	Running
2									Price 1	4	0
3									Price 2	3	4
4	Cable Sets								Price 3	2	7
5	Cable Set 1	15 - 8185	1
6	Cable Set 2	18 - 8222	2
7	Cable Set 3	21 - 8259	3
8	Cable Set 4	24 - 8296	4
9	Cable Set 45	24 - 8293	4
10	Cable Set 46	27 - 8330	5
11	Cable Set 47	30 - 8367	6
12	Cable Set 44	21 - 89583	3
13	Cable Set 45	24 - 810952	4
14
15
Order

Cell Formulas
Range	Formula
J2:J15	J2	=IF(I2="","",COUNTIF(INDIRECT("'"&I2&"'!$C$5:$C$99"),">0"))
K2:K15	K2	=IF(I2="","",SUM($J$1:$J1))
A5:C15	A5	=IF(ROW()-ROW($A$4)>SUM($J$2:$J$99),"",INDEX(INDIRECT("'"&INDEX($I$2:$I$99,MATCH(ROW()-ROW($A$4)-1,$K$2:$K$99,1))&"'!$A$5:$c$99"),AGGREGATE(15,6,INDIRECT("'"&INDEX($I$2:$I$99,MATCH(ROW()-ROW($A$4)-1,$K$2:$K$99,1))&"'!$C$5:$C$99"),(ROW()-ROW($A$4))-(INDEX($K$2:$K$99,MATCH(ROW()-ROW($A$4)-1,$K$2:$K$99,1)))),COLUMN()))

Hi Toadstool,

Thanks for your kind reply.​

By going through your solution, I figured to make my solution simpler now.
Setp1: User press default button to make sure that the next person who will be using it will not make wrong entries. For this, I need a button in my main "select" sheet. Which will make all items (column B, F, M) =0 and delete all entries from the order selection sheet.
Step 2: Now instead of taking data from different sheets, I will take data only from my main sheet " select" and select the particular items column B, F, M and create the order selection sheet.
Step 3: The order Selection sheet will contain the description item and price.

Please find the attached reference sheet link- .reference sheet.

 

[Toadstool]

Hi Allrounder,
I don't do VBA so maybe wait for one of the VBA'ers to pick this up.