Pushing Data from Newly Created Sheets

[BijanBorazjani]

I need to be able to push the data from the Daily Sheets into Well Summary. The sheets are going to be dynamic in the sense that it is unknown how long a run will last. This may not be something that can be done, but it can't hurt to ask.

'D1R1' C26 and C27 need to be pushed to 'Well Summary'. In addition to being pushed, it needs to add all the hours per run. Here in lies the problem, Each day, a macro creates a new sheet and it is renamed depending on the day and run. Cell C13 will always have the correct run number. So if DxRx C13 matches to Well Summary A30:A44, I want to add all the Circ hrs and all the Conn hrs for that specific run.

Blank Daily.xlsm
	A	B	C	D	E
12	DATE:
13	RUN NUMBER:
14
15	MWD EQUIPMENTS IDENTIFICATION
16	SECTION	S/N	PREV. HRS.	24 HRS.	TOTAL HRS.
17	PULSER	SELECT RUN	0	0	0
18	GAMMA	SELECT RUN	0	0	0
19	BATTERY1	SELECT RUN	0	0	0
20	ELECTRONICS	SELECT RUN	0	0	0
21	BATTERY2	SELECT RUN	0	0	0
22	MS SLEEVE	NA	0	0	0
23	POPPET-ORIFICE SIZE:		SELECT RUN	SELECT RUN	(inches)
24	PULSE WIDTH:		SELECT RUN		(seconds)
25	PULSE AMPLITUDE:				(p.s.i.)
26	TOTAL CONNECTED:				(last 24 hours)
27	TOTAL CIRC:				(last 24 hours)
D1R1

Cell Formulas
Range	Formula
D17	D17	=C27
D18	D18	=C27
D19	D19	=IF(D21>0,C27-D21,C27)
D20	D20	=C27
B17	B17	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!J$30,'Well Summary'!J$31,'Well Summary'!J$32,'Well Summary'!J$33,'Well Summary'!J$34,'Well Summary'!J$35,'Well Summary'!J$36,'Well Summary'!J$37,'Well Summary'!J$38,'Well Summary'!J$39,'Well Summary'!J$40,'Well Summary'!J$41,'Well Summary'!J$42,'Well Summary'!J$43,'Well Summary'!J$44))
B18	B18	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!K$30,'Well Summary'!K$31,'Well Summary'!K$32,'Well Summary'!K$33,'Well Summary'!K$34,'Well Summary'!K$35,'Well Summary'!K$36,'Well Summary'!K$37,'Well Summary'!K$38,'Well Summary'!K$39,'Well Summary'!K$40,'Well Summary'!K$41,'Well Summary'!K$42,'Well Summary'!K$43,'Well Summary'!K$44))
B19	B19	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!L$30,'Well Summary'!L$31,'Well Summary'!L$32,'Well Summary'!L$33,'Well Summary'!L$34,'Well Summary'!L$35,'Well Summary'!L$36,'Well Summary'!L$37,'Well Summary'!L$38,'Well Summary'!L$39,'Well Summary'!L$40,'Well Summary'!L$41,'Well Summary'!L$42,'Well Summary'!L$43,'Well Summary'!L$44))
B20	B20	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!M$30,'Well Summary'!M$31,'Well Summary'!M$32,'Well Summary'!M$33,'Well Summary'!M$34,'Well Summary'!M$35,'Well Summary'!M$36,'Well Summary'!M$37,'Well Summary'!M$38,'Well Summary'!M$39,'Well Summary'!M$40,'Well Summary'!M$41,'Well Summary'!M$42,'Well Summary'!M$43,'Well Summary'!M$44))
B21	B21	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!N$30,'Well Summary'!N$31,'Well Summary'!N$32,'Well Summary'!N$33,'Well Summary'!N$34,'Well Summary'!N$35,'Well Summary'!N$36,'Well Summary'!N$37,'Well Summary'!N$38,'Well Summary'!N$39,'Well Summary'!N$40,'Well Summary'!N$41,'Well Summary'!N$42,'Well Summary'!N$43,'Well Summary'!N$44))
E17:E22	E17	=D17+C17
D22	D22	=C27
C23:D23	C23	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!T30,'Well Summary'!T31,'Well Summary'!T32,'Well Summary'!T33,'Well Summary'!T34,'Well Summary'!T35,'Well Summary'!T36,'Well Summary'!T37,'Well Summary'!T38,'Well Summary'!T39,'Well Summary'!T40,'Well Summary'!T41,'Well Summary'!T42,'Well Summary'!T43,'Well Summary'!T44))
C24	C24	=IF($C$13="","SELECT RUN",CHOOSE($C$13,'Well Summary'!V30,'Well Summary'!V31,'Well Summary'!V32,'Well Summary'!V33,'Well Summary'!V34,'Well Summary'!V35,'Well Summary'!V36,'Well Summary'!V37,'Well Summary'!V38,'Well Summary'!V39,'Well Summary'!V40,'Well Summary'!V41,'Well Summary'!V42,'Well Summary'!V43,'Well Summary'!V44))

Cells with Data Validation
Cell	Allow	Criteria
C12	List	=$W$7:$W$87
C13	List	=$T$7:$T$22
C27:D27	Whole number	<24.1

Blank Daily.xlsm
	A	B	C	D	E	F	G
28	Run #	Fail Y/N	BRT Date & Time	ART Date & Time	Run Hours	Circ Hours	Conn Hours
29
30	1
31	2
32	3
33	4
34	5
35	6
36	7
37	8
38	9
39	10
40	11
41	12
42	13
43	14
44	15
Well Summary

Cell Formulas
Range	Formula
E30:E44	E30	=IF(D30="","",(D30-C30)*24)

 

[bobaol]

Hi, try this... assume you're able to modify the spreadsheet slightly by adding items to the empty columns to the right. in Sheet D1R1, create a UniqueID in cell E2. Do this by concatenating Date and Run Number (C12&C13). assume your Run number is in cell C24, Conn in C26, and Circ in C27. but you do not need to use those cells for your work. You can reference those cells in a row, by referencing them in J12, K12, L12. Now, those 3 numbers are in columns instead of rows. This will be important when doing a lookup. In sheet Well Summary, assume column H and to the right are all blank. In sheet Well Summary, add a date to H30 to H44 (you can use a macro to do this, or simply reference H30 to cell C12 in D1R1). In cell I30, concatenate Date and Run Number to get a unique number. Do your work and messy stuff in J30, K30, L30. Then have E30 F30 G30 reference J30, K30, L30. this will make it look prettier. The key here is you can use the blank areas in the spreadsheet to do your messy stuff, then in your pretty area, simply reference it. oh, in the Index(Match formula, the ,10 refers to column J, ,11 refers to column K, ,12 refers to column L. you can do it differently, but I always start in A and end at BBW. I start at A because A is always 1. Therefore, any column past A is really easy to reference. hope this helps. Cheers!

'''this is sheet D1R1.

'Cell A10	''	''	''	''	''	''	''	''	''Cell J10	''	''
'Cell A11, sheet D1R1
	Date:	32663	UniqueID:	=C12&C13					=C24	=C26	=C27
	Run Number:	3
		run sec
		Conn hr
		Circ hr

'''''this is sheet Well Summary

''cell A27 in sheet Well Summary	b27	c27	d27	e27	f27	g27	h27 date	i27 concatenate for UniqueID:	''	''	''
Run Number:				Run	Circ	Conn	Date:		run sec	Conn hr	Circ hr
1				=IF(ISERROR(J30),"",J30)	=IF(ISERROR(K30),"",K30)	=IF(ISERROR(L30),"",L30)	32663	=H30&A30	=INDEX(D1R1!A:BBW,MATCH(I30,D1R1!E:E,0),10)	=INDEX(D1R1!A:BBW,MATCH(I30,D1R1!E:E,0),11)	=INDEX(D1R1!A:BBW,MATCH(I30,D1R1!E:E,0),12)
2				=IF(ISERROR(J31),"",J31)	=IF(ISERROR(K31),"",K31)	=IF(ISERROR(L31),"",L31)	32663	=H31&A31	=INDEX(D1R1!A:BBW,MATCH(I31,D1R1!E:E,0),10)	=INDEX(D1R1!A:BBW,MATCH(I31,D1R1!E:E,0),11)	=INDEX(D1R1!A:BBW,MATCH(I31,D1R1!E:E,0),12)
3				=IF(ISERROR(J32),"",J32)	=IF(ISERROR(K32),"",K32)	=IF(ISERROR(L32),"",L32)	32663	=H32&A32	=INDEX(D1R1!A:BBW,MATCH(I32,D1R1!E:E,0),10)	=INDEX(D1R1!A:BBW,MATCH(I32,D1R1!E:E,0),11)	=INDEX(D1R1!A:BBW,MATCH(I32,D1R1!E:E,0),12)