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

gene_sc

New Member
Joined
Aug 24, 2002
Messages
32
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sorting or rearranging?
For example:
Book1
ABCDE
112345
212345
312345
412345
512345
612345
712345
812345
912345
1012345
1112345
1212345
1312345
1412345
1512345
1612345
1712345
Sheet1
Book1
ABCDE
125413
225413
325413
425413
525413
625413
725413
825413
925413
1025413
1125413
1225413
1325413
1425413
1525413
1625413
1725413
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Sheet 1 Starting point
EOD_Current.xls
ABCDEFGHIJKLMNOPQRST
1378OK71202D142311SAL121CASHBVIPCASHCUSTOMER-13.5600-1.0614.62ManualInvoice#Check#1944
23108OK71202D142336SAL121CASHBVIPCASHCUSTOMER-14.3700-1.1215.49ManualInvoice#Check#3420
34132OK71202D142346SAL121CASHCASHCUSTOMER-27.6800-2.1629.84ManualInvoice#Check#18325
43143OK71202D142356SAL121CASHCASHCUSTOMER-13.2300-1.0314.26ManualInvoice#Check#4237
54169OK71202D142359SAL121CASHCASHCUSTOMER-57.2400-4.4661.7ManualInvoice#Check#4802JM
63236OK71202D142379SAL121CASHBVIPCASHCUSTOMER-58.9400-4.663.54ManualInvoice#Check#1104
722610OK71202D142437SAL121CASHBVIPCASHCUSTOMER-42.3200-3.345.62ManualInvoice#Check#3601
83338OK71202D142446SAL121CASHBVIPCASHCUSTOMER-4.0300-0.314.34ManualInvoice#Check#14459
93298Voided71202D142434SAL1218690VICSPITALI-49.0100-3.8252.83ManualInvoice#Check#8889
Checks

This message was edited by gene_sc on 2002-08-25 13:46
 
Upvote 0
Sheet 2Headers added from template as well as some of the cell data
groupitem_with_salestax_mod2..xlt
ABCDEFGHIJKLMNOPQR
33!TRNSTRNSIDTRNSTYPEDATEACCNTNAMECLASSAMOUNTDOCNUMMEMOCLEARTOPRINTNAMEISTAXABLEADDR1ADDR2ADDR3ADDR4OTHER1
34!SPLSPLIDTRNSTYPEDATEACCNTNAMECLASSAMOUNTDOCNUMMEMOCLEARQNTYPRICEINVITEMPAYMETHTAXABLEVALADJOTHER2
35!ENDTRNS
36TRNSCASHSALE7/28/2002UndepositedFundsCashBSAL1,500.00D14324ManualInvoice#Check#1941NNYVIPCustomerTXTS1
37SPLCASHSALE7/28/2002NDep_InvoicesYN6
38SPLCASHSALE7/28/2002Parts-750NMdse11YN
39SPLCASHSALE7/28/2002Parts-750NCoreYN
40SPLCASHSALE7/28/2002ShopLabor75NShopLaborYN
41SPLCASHSALE7/28/2002-1,425.00thisisagroupNNN
42SPLCASHSALE7/28/2002SalesTaxPayableAnacortes-75D14324SalesTaxN7.80%salestaxNN
43ENDTRNS
groupitem_with_salestax_mod2

This message was edited by gene_sc on 2002-08-25 13:54
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,414
Messages
6,165,891
Members
451,992
Latest member
kaurmanjodh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top