transposing-- is that what this is called??

cocosoft · May 5th, 2002, 07:02 PM

I have written a spreadsheet for a mates small business.
He saves a seperate workbook for each customer.
What i want to be able to do is use a batch file or macro ?
I don't know which would be the best way to go- to open each customers sheet in turn, read the contents of 5 different cells
representing GST, Total, Customer name, date, invoice No. and transpose them to a seperate sheet for the purpose of keeping the tax department records.

All workbooks are saved in My Documents to save confusion for my customer
All filenames contain an element in the filename which is (-UT) for example
customer-UT0001.xls which could be used in a wildcard search to search through
the filenames in the My Documents folder.

My other problem is that i need to be sure details added from each sheet will not be duplicated when macro or batch file is subsequently run over again.
Cell references are Date A3 , Customer name B12 , GST F28, Total I28, Invoice no I3.
I hope there is enough information here for someone to be able to help me.

I would like to be able to run the macro/batch every 3, 6, or 12 months,
transpose all the necessary information onto a new sheet. No cell references
etc have been defined for the new sheet as of yet but the name for the sheet
will be UT-gst.xls References running straight down the page A1:A1000 would be quite OK.
All help will be appreciated
Regards to all members Coco.

Nimrod · May 5th, 2002, 07:49 PM

This sounds like an awkward way to keep these records. Have you thought about putting this all in one workbook ?
For example have one sheet that is your entry invoice sheet. Another sheet would be a centralized database with each row being a data record. The data sheet has a seperate column for each aspect of invoice ColA = invoice number, ColB Customer, ColC item , ColD = Cost etc etc.
There would be a seperate row for every entry into an invioce.
When you make up an invoice you fill out sheet one and then print it out and save to your data sheet.

The nice thing about doing it this way is that you have a centralized data structure that could be queried with pivot tables and excel formulas. The solution your intending to use is not very scalable.

Hope you don't mind my opinion on this mater.

Anne Troy · May 5th, 2002, 08:34 PM

I like your opinion too, Nimrod. Here's a file you can download...and it works.

http://www.thewordexpert.com/tipwarez.htm#MyInvoicing

cocosoft · May 6th, 2002, 05:58 PM

I Do appreciate your remarks Nimrod, I'm no whiz at Excel. My mate is quite happy with what I have produced for him thus har but I'm working at the limit of my knowledge at present. I'll try and use your suggestions to accomplish what I wish to do & let you know how i go with it.
It would be Ok I'm sure however i think i need to find out how to just save the appropriate selection of cells at the beginning of my workbook as the asctual individual customers record & I'm not at all sure how to do that. Do you understand what I'm trying to say?.
Thanx again Coco
On 2002-05-05 18:49, Nimrod wrote:
This sounds like an awkward way to keep these records. Have you thought about putting this all in one workbook ?
For example have one sheet that is your entry invoice sheet. Another sheet would be a centralized database with each row being a data record. The data sheet has a seperate column for each aspect of invoice ColA = invoice number, ColB Customer, ColC item , ColD = Cost etc etc.
There would be a seperate row for every entry into an invioce.
When you make up an invoice you fill out sheet one and then print it out and save to your data sheet.

The nice thing about doing it this way is that you have a centralized data structure that could be queried with pivot tables and excel formulas. The solution your intending to use is not very scalable.

Hope you don't mind my opinion on this mater.
[/quote]

cocosoft · May 6th, 2002, 06:02 PM

Thanx for your input too dreamboat! I've downloaded the file and will have a pl;ay with it & see what i can adapt drom it.
Appreciate your input. Cheers Coco

On 2002-05-05 19:34, Dreamboat wrote:
I like your opinion too, Nimrod. Here's a file you can download...and it works.

http://www.thewordexpert.com/tipwarez.htm#MyInvoicing

[/quote]

cocosoft · May 7th, 2002, 04:32 PM

Hi All
Further to my original question.
In the workbook I have created for my friend I have at present only 1 sheet however it is somewhat large being approx 360k. The sheet is a combined quote, invoice, & materials listing for a small company servicing approx 3 to 500 clients a year. It also contains full cost pricing and retail pricing structures for all materials used in my mates business.

At present after filling in the pertinent customer details in the cell range =A1:I46 ( the section of the sheet that is printed for the customer), the whole workbook is saved using customer name + quote number, thanks for you who helped me with that one.

I really would like to be able to just save the cell range =A1:I46 as the customers record for future reference and tax purposes.
1 Can I & how do I do that

Regarding my question about saving a cumulative total of each customers record fields, those being Customer name, Quote number, Total, G.S.T. in a seperate workbook.
It has been suggested that the way to do that would be best done using a second sheet in the current workbook that I'm using.

I can't figure out how to do that, being that at present i run a macro to clear the customers details after doing the save as, & then I proceed to input the next customers figures & so on.

2 To have a second sheet within the workbook keeping an accurate cumulative record of the fields above after i zero out my customers records to re-use the workbook for subsequent customers, well I just don't understand how that can be done.

That's why i originally asked how I could use a macro or batch file to open all the saved customer records in turn and transpose all those cell references into a new sheet to keep my cumulative total for the year of all GST payable to the tax department.

God I hope this explanation is clear enough so that someone can offer me some further help with it. I think the second sheet within the workbook for my cumulative totals would work if I could just save the =A1:I46 range as the customers file name & then make absolute cell references to those sheets. Phew My head is spinning just trying to formulate a clear question.
HElp Please. Coco

[ This Message was edited by: cocosoft on 2002-05-07 15:56 ]

Yogi Anand · May 7th, 2002, 05:28 PM

Hi cocosoft:
Your setup for customer info, invoicing etc., no matter how you look at it is a databse operation. In Excel, we may call it a list.
The ideal way to do it to have different lists, one for customer info, one for invoicing, ... , and one for transactions. In the database approach, creating an invoice should be with formulas, pulling up all the pertinent information from the associated lists. And then finally, the transaction data should be stored for record purposes.
What I am trying to say is that all your data needs to be saved anyway, normally it will be the invoice part that would be formatted because that is the formal submission to the customer. There is no need to save every invoice that would be generated, since all the associated data for that should exist in the associated lists anyway. Your setup should be such that you can create/recreate an invoice any time.
I hope I am making sense to you. If you are with me on this one, and you need further clarification on this one, I will be glad to look at your workbook, and provide the necessary feedback.

Regards!

cocosoft · May 7th, 2002, 05:58 PM

Thanks ever so much for the offer to have a look at my workbook for me Yogi, from my point of view and where i am on the learning curve i would very much like your input. My mate has already gained quite an advantage over some compeditors in the marketplace as a result of his more streamlined quote/invoice system I have thus far produced for him. He has been fighting off advances from several who want to get their hands on a copy. Long story shortened, he doesn't want me showing it to anyone else as it represents over 3 months work.
If it were just up to me I would love to show it to You, looks like I'm going to have to get all the side issues fixed the hard way learning as i go, hopefully with continued help from all you great guys there.
Hope you're not offended by this.
Regards Coco
ps looks like a complete re-write, splitting up all the seperate elements onto seperate sheets. I get what you have been trying to tell me i think.
On 2002-05-07 16:28, Yogi Anand wrote:
Hi cocosoft:
Your setup for customer info, invoicing etc., no matter how you look at it is a databse operation. In Excel, we may call it a list.
The ideal way to do it to have different lists, one for customer info, one for invoicing, ... , and one for transactions. In the database approach, creating an invoice should be with formulas, pulling up all the pertinent information from the associated lists. And then finally, the transaction data should be stored for record purposes.
What I am trying to say is that all your data needs to be saved anyway, normally it will be the invoice part that would be formatted because that is the formal submission to the customer. There is no need to save every invoice that would be generated, since all the associated data for that should exist in the associated lists anyway. Your setup should be such that you can create/recreate an invoice any time.
I hope I am making sense to you. If you are with me on this one, and you need further clarification on this one, I will be glad to look at your workbook, and provide the necessary feedback.

Regards!

[/quote]

RichardS · May 7th, 2002, 06:18 PM

Coco,
I think what the other guys are suggesting is that you have a sheet that, on each row, contains all the details yoou require, such as customer number, quote no., invoice no, date, amount, GST etc. You would then have a separate sheet in the same workbook that would be formatted the same as your existing quote/invoice. You would then have a unique record identifier (probably invoice no.) which you could enter on a non-printed part of the invoice. All the fields you want to print on the invoice would then use this identifier to "LOOKUP" the relevant record on your data sheet and return the correct information. The advantage then is that you can use filters on the data sheet to return only the information you need, say invoices for a quarter to get the GST for your BAS.

Is this a little clearer?

Richard

cocosoft · May 7th, 2002, 07:08 PM

Thanks for your remarks Richard bur Coco is starting to get a real headache. All you helpful people have overwhelmed me and i am just trying to take it all in. Can't say things are much clearer but think I know the direction I'm going to take. Reckon I need to radically restructure the way i have done my workbook and split up all the main elements onto sheets of their own and tie them back together or al least re-do all my references to point to the new sheets.
What i have written is already very complex with some helishingly complex formulas involved. I think I will get there though if I just bite off small mouthfulls at a time.
99% of my references are relative so I might be able to get by with a fair bit of cut & pasting.
Thanx again for your input
Regards Coco :-}
On 2002-05-07 17:18, RichardS wrote:
Coco,
I think what the other guys are suggesting is that you have a sheet that, on each row, contains all the details yoou require, such as customer number, quote no., invoice no, date, amount, GST etc. You would then have a separate sheet in the same workbook that would be formatted the same as your existing quote/invoice. You would then have a unique record identifier (probably invoice no.) which you could enter on a non-printed part of the invoice. All the fields you want to print on the invoice would then use this identifier to "LOOKUP" the relevant record on your data sheet and return the correct information. The advantage then is that you can use filters on the data sheet to return only the information you need, say invoices for a quarter to get the GST for your BAS.

Is this a little clearer?

Richard
[/quote]

May 5th, 2002, 08:34 PM	#3
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	I like your opinion too, Nimrod. Here's a file you can download...and it works. http://www.thewordexpert.com/tipwarez.htm#MyInvoicing __________________ ~Anne Troy

May 7th, 2002, 05:28 PM	#7
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi cocosoft: Your setup for customer info, invoicing etc., no matter how you look at it is a databse operation. In Excel, we may call it a list. The ideal way to do it to have different lists, one for customer info, one for invoicing, ... , and one for transactions. In the database approach, creating an invoice should be with formulas, pulling up all the pertinent information from the associated lists. And then finally, the transaction data should be stored for record purposes. What I am trying to say is that all your data needs to be saved anyway, normally it will be the invoice part that would be formatted because that is the formal submission to the customer. There is no need to save every invoice that would be generated, since all the associated data for that should exist in the associated lists anyway. Your setup should be such that you can create/recreate an invoice any time. I hope I am making sense to you. If you are with me on this one, and you need further clarification on this one, I will be glad to look at your workbook, and provide the necessary feedback. Regards! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 5th, 2002, 07:02 PM	#1
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	I have written a spreadsheet for a mates small business. He saves a seperate workbook for each customer. What i want to be able to do is use a batch file or macro ? I don't know which would be the best way to go- to open each customers sheet in turn, read the contents of 5 different cells representing GST, Total, Customer name, date, invoice No. and transpose them to a seperate sheet for the purpose of keeping the tax department records. All workbooks are saved in My Documents to save confusion for my customer All filenames contain an element in the filename which is (-UT) for example customer-UT0001.xls which could be used in a wildcard search to search through the filenames in the My Documents folder. My other problem is that i need to be sure details added from each sheet will not be duplicated when macro or batch file is subsequently run over again. Cell references are Date A3 , Customer name B12 , GST F28, Total I28, Invoice no I3. I hope there is enough information here for someone to be able to help me. I would like to be able to run the macro/batch every 3, 6, or 12 months, transpose all the necessary information onto a new sheet. No cell references etc have been defined for the new sheet as of yet but the name for the sheet will be UT-gst.xls References running straight down the page A1:A1000 would be quite OK. All help will be appreciated Regards to all members Coco.

May 5th, 2002, 07:49 PM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	This sounds like an awkward way to keep these records. Have you thought about putting this all in one workbook ? For example have one sheet that is your entry invoice sheet. Another sheet would be a centralized database with each row being a data record. The data sheet has a seperate column for each aspect of invoice ColA = invoice number, ColB Customer, ColC item , ColD = Cost etc etc. There would be a seperate row for every entry into an invioce. When you make up an invoice you fill out sheet one and then print it out and save to your data sheet. The nice thing about doing it this way is that you have a centralized data structure that could be queried with pivot tables and excel formulas. The solution your intending to use is not very scalable. Hope you don't mind my opinion on this mater.

May 6th, 2002, 05:58 PM	#4
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	I Do appreciate your remarks Nimrod, I'm no whiz at Excel. My mate is quite happy with what I have produced for him thus har but I'm working at the limit of my knowledge at present. I'll try and use your suggestions to accomplish what I wish to do & let you know how i go with it. It would be Ok I'm sure however i think i need to find out how to just save the appropriate selection of cells at the beginning of my workbook as the asctual individual customers record & I'm not at all sure how to do that. Do you understand what I'm trying to say?. Thanx again Coco On 2002-05-05 18:49, Nimrod wrote: This sounds like an awkward way to keep these records. Have you thought about putting this all in one workbook ? For example have one sheet that is your entry invoice sheet. Another sheet would be a centralized database with each row being a data record. The data sheet has a seperate column for each aspect of invoice ColA = invoice number, ColB Customer, ColC item , ColD = Cost etc etc. There would be a seperate row for every entry into an invioce. When you make up an invoice you fill out sheet one and then print it out and save to your data sheet. The nice thing about doing it this way is that you have a centralized data structure that could be queried with pivot tables and excel formulas. The solution your intending to use is not very scalable. Hope you don't mind my opinion on this mater. [/quote]

May 6th, 2002, 06:02 PM	#5
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Thanx for your input too dreamboat! I've downloaded the file and will have a pl;ay with it & see what i can adapt drom it. Appreciate your input. Cheers Coco On 2002-05-05 19:34, Dreamboat wrote: I like your opinion too, Nimrod. Here's a file you can download...and it works. http://www.thewordexpert.com/tipwarez.htm#MyInvoicing [/quote]

May 7th, 2002, 04:32 PM	#6
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Hi All Further to my original question. In the workbook I have created for my friend I have at present only 1 sheet however it is somewhat large being approx 360k. The sheet is a combined quote, invoice, & materials listing for a small company servicing approx 3 to 500 clients a year. It also contains full cost pricing and retail pricing structures for all materials used in my mates business. At present after filling in the pertinent customer details in the cell range =A1:I46 ( the section of the sheet that is printed for the customer), the whole workbook is saved using customer name + quote number, thanks for you who helped me with that one. I really would like to be able to just save the cell range =A1:I46 as the customers record for future reference and tax purposes. 1 Can I & how do I do that Regarding my question about saving a cumulative total of each customers record fields, those being Customer name, Quote number, Total, G.S.T. in a seperate workbook. It has been suggested that the way to do that would be best done using a second sheet in the current workbook that I'm using. I can't figure out how to do that, being that at present i run a macro to clear the customers details after doing the save as, & then I proceed to input the next customers figures & so on. 2 To have a second sheet within the workbook keeping an accurate cumulative record of the fields above after i zero out my customers records to re-use the workbook for subsequent customers, well I just don't understand how that can be done. That's why i originally asked how I could use a macro or batch file to open all the saved customer records in turn and transpose all those cell references into a new sheet to keep my cumulative total for the year of all GST payable to the tax department. God I hope this explanation is clear enough so that someone can offer me some further help with it. I think the second sheet within the workbook for my cumulative totals would work if I could just save the =A1:I46 range as the customers file name & then make absolute cell references to those sheets. Phew My head is spinning just trying to formulate a clear question. HElp Please. Coco [ This Message was edited by: cocosoft on 2002-05-07 15:56 ]

May 7th, 2002, 05:58 PM	#8
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Thanks ever so much for the offer to have a look at my workbook for me Yogi, from my point of view and where i am on the learning curve i would very much like your input. My mate has already gained quite an advantage over some compeditors in the marketplace as a result of his more streamlined quote/invoice system I have thus far produced for him. He has been fighting off advances from several who want to get their hands on a copy. Long story shortened, he doesn't want me showing it to anyone else as it represents over 3 months work. If it were just up to me I would love to show it to You, looks like I'm going to have to get all the side issues fixed the hard way learning as i go, hopefully with continued help from all you great guys there. Hope you're not offended by this. Regards Coco ps looks like a complete re-write, splitting up all the seperate elements onto seperate sheets. I get what you have been trying to tell me i think. On 2002-05-07 16:28, Yogi Anand wrote: Hi cocosoft: Your setup for customer info, invoicing etc., no matter how you look at it is a databse operation. In Excel, we may call it a list. The ideal way to do it to have different lists, one for customer info, one for invoicing, ... , and one for transactions. In the database approach, creating an invoice should be with formulas, pulling up all the pertinent information from the associated lists. And then finally, the transaction data should be stored for record purposes. What I am trying to say is that all your data needs to be saved anyway, normally it will be the invoice part that would be formatted because that is the formal submission to the customer. There is no need to save every invoice that would be generated, since all the associated data for that should exist in the associated lists anyway. Your setup should be such that you can create/recreate an invoice any time. I hope I am making sense to you. If you are with me on this one, and you need further clarification on this one, I will be glad to look at your workbook, and provide the necessary feedback. Regards! [/quote]

May 7th, 2002, 06:18 PM	#9
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	Coco, I think what the other guys are suggesting is that you have a sheet that, on each row, contains all the details yoou require, such as customer number, quote no., invoice no, date, amount, GST etc. You would then have a separate sheet in the same workbook that would be formatted the same as your existing quote/invoice. You would then have a unique record identifier (probably invoice no.) which you could enter on a non-printed part of the invoice. All the fields you want to print on the invoice would then use this identifier to "LOOKUP" the relevant record on your data sheet and return the correct information. The advantage then is that you can use filters on the data sheet to return only the information you need, say invoices for a quarter to get the GST for your BAS. Is this a little clearer? Richard

May 7th, 2002, 07:08 PM	#10
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Thanks for your remarks Richard bur Coco is starting to get a real headache. All you helpful people have overwhelmed me and i am just trying to take it all in. Can't say things are much clearer but think I know the direction I'm going to take. Reckon I need to radically restructure the way i have done my workbook and split up all the main elements onto sheets of their own and tie them back together or al least re-do all my references to point to the new sheets. What i have written is already very complex with some helishingly complex formulas involved. I think I will get there though if I just bite off small mouthfulls at a time. 99% of my references are relative so I might be able to get by with a fair bit of cut & pasting. Thanx again for your input Regards Coco :-} On 2002-05-07 17:18, RichardS wrote: Coco, I think what the other guys are suggesting is that you have a sheet that, on each row, contains all the details yoou require, such as customer number, quote no., invoice no, date, amount, GST etc. You would then have a separate sheet in the same workbook that would be formatted the same as your existing quote/invoice. You would then have a unique record identifier (probably invoice no.) which you could enter on a non-printed part of the invoice. All the fields you want to print on the invoice would then use this identifier to "LOOKUP" the relevant record on your data sheet and return the correct information. The advantage then is that you can use filters on the data sheet to return only the information you need, say invoices for a quarter to get the GST for your BAS. Is this a little clearer? Richard [/quote]