UpDated... How to Change Row Offset in pasted formulas

[gene_sc]

Hi All-
I am using excel 2000 to import and format a tab delimited txt file(*.txt will always be in the same format) for import to QuickBooks
I am looking for a way to sort columns into a different order-on a new worksheet(I have tried using Named Ranges and refer to them on the new sheet)- but seem to missing something - I am looking to automate this step in conversion of the text file
thanks for your time in answering my question
regards gene
This message was edited by gene_sc on 2002-08-25 10:38
This message was edited by gene_sc on 2002-08-28 18:29

 

[Legacy 98055]

Sorting or rearranging?
For example:

Book1
	A	B	C	D	E
1	1	2	3	4	5
2	1	2	3	4	5
3	1	2	3	4	5
4	1	2	3	4	5
5	1	2	3	4	5
6	1	2	3	4	5
7	1	2	3	4	5
8	1	2	3	4	5
9	1	2	3	4	5
10	1	2	3	4	5
11	1	2	3	4	5
12	1	2	3	4	5
13	1	2	3	4	5
14	1	2	3	4	5
15	1	2	3	4	5
16	1	2	3	4	5
17	1	2	3	4	5
Sheet1

Book1
	A	B	C	D	E
1	2	5	4	1	3
2	2	5	4	1	3
3	2	5	4	1	3
4	2	5	4	1	3
5	2	5	4	1	3
6	2	5	4	1	3
7	2	5	4	1	3
8	2	5	4	1	3
9	2	5	4	1	3
10	2	5	4	1	3
11	2	5	4	1	3
12	2	5	4	1	3
13	2	5	4	1	3
14	2	5	4	1	3
15	2	5	4	1	3
16	2	5	4	1	3
17	2	5	4	1	3
Sheet1

Please list which columns are going where???
Sheet1 Column A to Sheet2 Column C
Sheet1 Column B to Sheet2 Column A
ect...
Tom
This message was edited by TsTom on 2002-08-25 11:38

 

[gene_sc]

Hi Tom
thanks for the reply-Your example would get me started :^)
Hope I am explaining this correctly
I need to loop by rows adding headers from template after each row then sort that row
Sheet 1 source-A:P (13 columns of data)
# of rows changes for each report
Sheet 2 23 Columns Headers-from template

Then move columns
Sheet1 to Sheet2
A1 to B1
B1 to R2
C1 to -not needed filled from template
D1 to D1
E1 to I1
F1 to G1
G1 to V1
H1 to R1
I1 to F1
J1 to N1
K1 to H6
L1 to H7
M1 to H8
N1 to H9
O1 to H10
P1 to H4
then loop thru next row adding headers (from my template) for each transaction line
regards gene
This message was edited by gene_sc on 2002-08-25 12:59

 

[Legacy 98055]

Hi Gene.
Perhaps you might download the HTML utility created by several members of this board to display a before and after, sheet1 to sheet2 results of your procedure. That is how the spreadsheets were displayed above. I do not clearly understand your goal.
Thanks,
Tom

http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm

 

[gene_sc]

Hi
just found the utility so I will post them shortly.
What you did in your sample would be a starting point-but I think I need to break it down by rows(I need to add headers for each row of data). I think I can create a maro to loop thru the whole sheet to finish adding the headers and data. It's the sorting of the columns thats got me stumped-so far
thanks once again
gene

 

[gene_sc]

Sheet 1 Starting point

EOD_Current.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	37	8	OK	71202	D142311	SAL	12	1	CASHB	VIP	CASHCUSTOMER	-13.56	0	0	-1.06	14.62	ManualInvoice#Check#1944
2	310	8	OK	71202	D142336	SAL	12	1	CASHB	VIP	CASHCUSTOMER	-14.37	0	0	-1.12	15.49	ManualInvoice#Check#3420
3	413	2	OK	71202	D142346	SAL	12	1	CASH	CAS	HCUSTOMER	-27.68	0	0	-2.16	29.84	ManualInvoice#Check#18325
4	314	3	OK	71202	D142356	SAL	12	1	CASH	CAS	HCUSTOMER	-13.23	0	0	-1.03	14.26	ManualInvoice#Check#4237
5	416	9	OK	71202	D142359	SAL	12	1	CASH	CAS	HCUSTOMER	-57.24	0	0	-4.46	61.7	ManualInvoice#Check#4802JM
6	323	6	OK	71202	D142379	SAL	12	1	CASHB	VIP	CASHCUSTOMER	-58.94	0	0	-4.6	63.54	ManualInvoice#Check#1104
7	226	10	OK	71202	D142437	SAL	12	1	CASHB	VIP	CASHCUSTOMER	-42.32	0	0	-3.3	45.62	ManualInvoice#Check#3601
8	333	8	OK	71202	D142446	SAL	12	1	CASHB	VIP	CASHCUSTOMER	-4.03	0	0	-0.31	4.34	ManualInvoice#Check#14459
9	329	8	Voided	71202	D142434	SAL	12	1	8690	VIC	SPITALI	-49.01	0	0	-3.82	52.83	ManualInvoice#Check#8889
Checks

This message was edited by gene_sc on 2002-08-25 13:46

 

[gene_sc]

Sheet 2Headers added from template as well as some of the cell data

groupitem_with_salestax_mod2..xlt
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
33	!TRNS	TRNSID	TRNSTYPE	DATE	ACCNT	NAME	CLASS	AMOUNT	DOCNUM	MEMO	CLEAR	TOPRINT	NAMEISTAXABLE	ADDR1	ADDR2	ADDR3	ADDR4	OTHER1
34	!SPL	SPLID	TRNSTYPE	DATE	ACCNT	NAME	CLASS	AMOUNT	DOCNUM	MEMO	CLEAR	QNTY	PRICE	INVITEM	PAYMETH	TAXABLE	VALADJ	OTHER2
35	!ENDTRNS
36	TRNS		CASHSALE	7/28/2002	UndepositedFunds	CashB	SAL	1,500.00	D14324	ManualInvoice#Check#1941	N	N	Y	VIPCustomer				TXTS1
37	SPL		CASHSALE	7/28/2002							N			Dep_Invoices		Y	N	6
38	SPL		CASHSALE	7/28/2002	Parts			-750			N			Mdse	11	Y	N
39	SPL		CASHSALE	7/28/2002	Parts			-750			N			Core		Y	N
40	SPL		CASHSALE	7/28/2002	ShopLabor			75			N			ShopLabor		Y	N
41	SPL		CASHSALE	7/28/2002				-1,425.00		thisisagroup	N					N	N
42	SPL		CASHSALE	7/28/2002	SalesTaxPayable	Anacortes		-75	D14324	SalesTax	N		7.80%	salestax		N	N
43	ENDTRNS
groupitem_with_salestax_mod2

This message was edited by gene_sc on 2002-08-25 13:54

 

[Legacy 98055]

Hi Gene.
I'm feeling a bit dense. You have gone out of your way to communicate your problem and I am still not understanding???
If you don't mind, send me the sheets, B4 & after, along with any other helpful details.
Thanks,
Tom

TsTom@hotmail.com

 

[gene_sc]

Hi Tom
I e-mailed a sample to you this evening.. But forgot a few details. What I am thinking is I need to just paste my template,with the formula added after the Header Info first 32 Rows with the first loop, (of the macro). I have hard coded 9 transactions-But when I copy the formula for range of cells, to the next blank cells below, It( the formula current row) refers to it's row number, in active sheet instead of referencing the row of the source sheet data. -Cell Offset need to be added to formula??
thanks
gene
This message was edited by gene_sc on 2002-08-26 19:03

 

[gene_sc]

Hi all
I have the cell references for the 1 row of source data. The problem is when the formulas are pasted, the cell offset is wrong. The same formulas below explain...(I hope :^))
The samples below are all in the same column separated by 7 rows of data while the source sheet is single rows<LIST><L1>=Checks!R[-4]C[-1]'pasted formula sample-ok for row 1 in source sheet<L2>=Checks!R[-4]C[-1]'pasted formula sample- for row 2 in source sheet<L3>'Offset needs to change to formula below<L4>=Checks!R[-11]C[-1]'pasted formula sample-sheet1 row 2 formula<L5>'Third Time pasted offset needed
=Checks!R[-18]C[-1]'sheet1 Row 3 corrected offset</LIST>
thanks gene
This message was edited by gene_sc on 2002-08-28 18:18