What's the best way to...

Hi.  I think this is possible, but I don't know how to go about it or where 
exactly to look.

I have a multi-worksheet workbook that is around 2.4 mb in size.  The user 
inputs a relatively small amount of information on a few of the worksheets.  
The rest of the worksheets hold fixed data used for performing various 
calculations or building reports.  For each separate use, the user is 
performing a 'Save As'.  I have one user who is creating up to 20 of these 
files each day which is really filling up their hard-drive.  

I'd like to have a solution to this problem and I'm guessing that if all of 
the 'input' cells were to be exported to a comma delimited text file, the 
text file would be very small in comparison.  Likewise, if the text file were 
imported, all of the calculations and print functions would still work 
normally.

So, my question is: How do I go about this?

I'm sure the solution is VBA, but my VBA knowledge is very slim.

Any and all help is much appreciated.  TIA    :-)

-- 
rpw
0
rpw (18)
2/3/2005 5:25:02 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
447 Views

Similar Articles

[PageSpeed] 30

Hi again,

I've got an idea for the 'export' side of the problem.  I've created another 
worksheet for data collection where each cell equals one of the other 
worksheet's 'input' cells.  I'll then have a 'Save As .csv" routine run so 
the data collection sheet will be saved.  I'm working on building this now 
and I don't think this step will be any problem.

However, I don't quite know how to go about populating the blank template 
from one of the saved csv files.  Any help is appreciated.

Thanks in Advance

"rpw" wrote:

> Hi.  I think this is possible, but I don't know how to go about it or where 
> exactly to look.
> 
> I have a multi-worksheet workbook that is around 2.4 mb in size.  The user 
> inputs a relatively small amount of information on a few of the worksheets.  
> The rest of the worksheets hold fixed data used for performing various 
> calculations or building reports.  For each separate use, the user is 
> performing a 'Save As'.  I have one user who is creating up to 20 of these 
> files each day which is really filling up their hard-drive.  
> 
> I'd like to have a solution to this problem and I'm guessing that if all of 
> the 'input' cells were to be exported to a comma delimited text file, the 
> text file would be very small in comparison.  Likewise, if the text file were 
> imported, all of the calculations and print functions would still work 
> normally.
> 
> So, my question is: How do I go about this?
> 
> I'm sure the solution is VBA, but my VBA knowledge is very slim.
> 
> Any and all help is much appreciated.  TIA    :-)
> 
> -- 
> rpw
0
rpw (18)
2/3/2005 7:49:05 PM
Questions in Column A.
Responses in column B.

Then when you save as .csv, you have essentially two columns.

Then you could either provide a macro that opens the .csv and copy|paste special
Values from the second column (of the .csv file) into the second column of your
input sheet.

But be careful.  If you insert new rows (questions), you might be putting the
wrong response with the wrong question.

I think I'd add a first question:  Version of Input Sheet.

Then you could build rules that say that question #8 is now question #10, and #8
and #9 will default to a new value.

So you'd have a conversion for each version (until a version gets old enough to
say that you're not supporting it any more.)

Good luck,

rpw wrote:
> 
> Hi again,
> 
> I've got an idea for the 'export' side of the problem.  I've created another
> worksheet for data collection where each cell equals one of the other
> worksheet's 'input' cells.  I'll then have a 'Save As .csv" routine run so
> the data collection sheet will be saved.  I'm working on building this now
> and I don't think this step will be any problem.
> 
> However, I don't quite know how to go about populating the blank template
> from one of the saved csv files.  Any help is appreciated.
> 
> Thanks in Advance
> 
> "rpw" wrote:
> 
> > Hi.  I think this is possible, but I don't know how to go about it or where
> > exactly to look.
> >
> > I have a multi-worksheet workbook that is around 2.4 mb in size.  The user
> > inputs a relatively small amount of information on a few of the worksheets.
> > The rest of the worksheets hold fixed data used for performing various
> > calculations or building reports.  For each separate use, the user is
> > performing a 'Save As'.  I have one user who is creating up to 20 of these
> > files each day which is really filling up their hard-drive.
> >
> > I'd like to have a solution to this problem and I'm guessing that if all of
> > the 'input' cells were to be exported to a comma delimited text file, the
> > text file would be very small in comparison.  Likewise, if the text file were
> > imported, all of the calculations and print functions would still work
> > normally.
> >
> > So, my question is: How do I go about this?
> >
> > I'm sure the solution is VBA, but my VBA knowledge is very slim.
> >
> > Any and all help is much appreciated.  TIA    :-)
> >
> > --
> > rpw

-- 

Dave Peterson
0
ec357201 (5290)
2/4/2005 12:44:00 AM
raw wrote:

>I have one user who is creating up to 20 of these 
> files each day which is really filling up their hard-drive.  

You can't "legislate" for this. The question has to be "WHY" does the 
user need to change this file 20 times a day, and WHY can they not 
either delete some of the old files or save them to a network drive 
instead? What is so important about the number of times the file is 
"saved as"?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk
0
gordonbp11 (453)
2/4/2005 12:54:38 AM
Hi Dave,

Thanks for responding so quickly.  

The version idea/problem is one I didn't think about before.  I'm now 
including the version number in the auto-save file name.  I don't expect too 
many changes to the layout, but rather to the hidden data - prices and such.

I think also that if the user clicks the import button I'd want the macro to 
check to see if there is data in the input cells and if there is then ask the 
user if they'd like to save the existing first.  If not then clear out any 
existing data and import from a selected csv file.  But those are things I'll 
have to ponder out.

I have other questions regarding the columns in the csv now.  My existing 
sheet uses up to 12 columns in some rows and 3 or 4 in other rows.  When I 
look at the file with "Notebook", some of those rows end with a comma and 
others don't and I don't know why.  I'm thinking that I might have made a 
mistake somewhere and should have just a single column.  Do you have any 
suggestions on where I might find some more info on this kind of thing?

thanks...

"Dave Peterson" wrote:

> Questions in Column A.
> Responses in column B.
> 
> Then when you save as .csv, you have essentially two columns.
> 
> Then you could either provide a macro that opens the .csv and copy|paste special
> Values from the second column (of the .csv file) into the second column of your
> input sheet.
> 
> But be careful.  If you insert new rows (questions), you might be putting the
> wrong response with the wrong question.
> 
> I think I'd add a first question:  Version of Input Sheet.
> 
> Then you could build rules that say that question #8 is now question #10, and #8
> and #9 will default to a new value.
> 
> So you'd have a conversion for each version (until a version gets old enough to
> say that you're not supporting it any more.)
> 
> Good luck,
> 
> rpw wrote:
> > 
> > Hi again,
> > 
> > I've got an idea for the 'export' side of the problem.  I've created another
> > worksheet for data collection where each cell equals one of the other
> > worksheet's 'input' cells.  I'll then have a 'Save As .csv" routine run so
> > the data collection sheet will be saved.  I'm working on building this now
> > and I don't think this step will be any problem.
> > 
> > However, I don't quite know how to go about populating the blank template
> > from one of the saved csv files.  Any help is appreciated.
> > 
> > Thanks in Advance
> > 
> > "rpw" wrote:
> > 
> > > Hi.  I think this is possible, but I don't know how to go about it or where
> > > exactly to look.
> > >
> > > I have a multi-worksheet workbook that is around 2.4 mb in size.  The user
> > > inputs a relatively small amount of information on a few of the worksheets.
> > > The rest of the worksheets hold fixed data used for performing various
> > > calculations or building reports.  For each separate use, the user is
> > > performing a 'Save As'.  I have one user who is creating up to 20 of these
> > > files each day which is really filling up their hard-drive.
> > >
> > > I'd like to have a solution to this problem and I'm guessing that if all of
> > > the 'input' cells were to be exported to a comma delimited text file, the
> > > text file would be very small in comparison.  Likewise, if the text file were
> > > imported, all of the calculations and print functions would still work
> > > normally.
> > >
> > > So, my question is: How do I go about this?
> > >
> > > I'm sure the solution is VBA, but my VBA knowledge is very slim.
> > >
> > > Any and all help is much appreciated.  TIA    :-)
> > >
> > > --
> > > rpw
> 
> -- 
> 
> Dave Peterson
> 
0
rpw (18)
2/4/2005 1:25:01 AM
You can count how many cells have something in them with something like:

dim resp as long
if application.countif(worksheets("sheet1").range("b:b")) > 0 then
   resp = msgbox(prompt:="overwrite existing?",buttons:=vbyesno)
   if resp = vbno then exit sub
end if

But those extra commas shouldn't hurt anything.  When you bring open the .csv,
excel will ignore them.

I think that this comes close to describing your problem:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q77295
XL: Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

(But after you import your .csv file, you'll see that you didn't have to worry
about this.)

rpw wrote:
> 
> Hi Dave,
> 
> Thanks for responding so quickly.
> 
> The version idea/problem is one I didn't think about before.  I'm now
> including the version number in the auto-save file name.  I don't expect too
> many changes to the layout, but rather to the hidden data - prices and such.
> 
> I think also that if the user clicks the import button I'd want the macro to
> check to see if there is data in the input cells and if there is then ask the
> user if they'd like to save the existing first.  If not then clear out any
> existing data and import from a selected csv file.  But those are things I'll
> have to ponder out.
> 
> I have other questions regarding the columns in the csv now.  My existing
> sheet uses up to 12 columns in some rows and 3 or 4 in other rows.  When I
> look at the file with "Notebook", some of those rows end with a comma and
> others don't and I don't know why.  I'm thinking that I might have made a
> mistake somewhere and should have just a single column.  Do you have any
> suggestions on where I might find some more info on this kind of thing?
> 
> thanks...
> 
> "Dave Peterson" wrote:
> 
> > Questions in Column A.
> > Responses in column B.
> >
> > Then when you save as .csv, you have essentially two columns.
> >
> > Then you could either provide a macro that opens the .csv and copy|paste special
> > Values from the second column (of the .csv file) into the second column of your
> > input sheet.
> >
> > But be careful.  If you insert new rows (questions), you might be putting the
> > wrong response with the wrong question.
> >
> > I think I'd add a first question:  Version of Input Sheet.
> >
> > Then you could build rules that say that question #8 is now question #10, and #8
> > and #9 will default to a new value.
> >
> > So you'd have a conversion for each version (until a version gets old enough to
> > say that you're not supporting it any more.)
> >
> > Good luck,
> >
> > rpw wrote:
> > >
> > > Hi again,
> > >
> > > I've got an idea for the 'export' side of the problem.  I've created another
> > > worksheet for data collection where each cell equals one of the other
> > > worksheet's 'input' cells.  I'll then have a 'Save As .csv" routine run so
> > > the data collection sheet will be saved.  I'm working on building this now
> > > and I don't think this step will be any problem.
> > >
> > > However, I don't quite know how to go about populating the blank template
> > > from one of the saved csv files.  Any help is appreciated.
> > >
> > > Thanks in Advance
> > >
> > > "rpw" wrote:
> > >
> > > > Hi.  I think this is possible, but I don't know how to go about it or where
> > > > exactly to look.
> > > >
> > > > I have a multi-worksheet workbook that is around 2.4 mb in size.  The user
> > > > inputs a relatively small amount of information on a few of the worksheets.
> > > > The rest of the worksheets hold fixed data used for performing various
> > > > calculations or building reports.  For each separate use, the user is
> > > > performing a 'Save As'.  I have one user who is creating up to 20 of these
> > > > files each day which is really filling up their hard-drive.
> > > >
> > > > I'd like to have a solution to this problem and I'm guessing that if all of
> > > > the 'input' cells were to be exported to a comma delimited text file, the
> > > > text file would be very small in comparison.  Likewise, if the text file were
> > > > imported, all of the calculations and print functions would still work
> > > > normally.
> > > >
> > > > So, my question is: How do I go about this?
> > > >
> > > > I'm sure the solution is VBA, but my VBA knowledge is very slim.
> > > >
> > > > Any and all help is much appreciated.  TIA    :-)
> > > >
> > > > --
> > > > rpw
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
ec357201 (5290)
2/4/2005 2:06:37 AM
Sorry for not getting back quickly.  Thank you for the bit of code and the 
link, they look like they'll help me alot.  I'll post back if I need more 
help.  Thanks again.

"Dave Peterson" wrote:

> You can count how many cells have something in them with something like:
> 
> dim resp as long
> if application.countif(worksheets("sheet1").range("b:b")) > 0 then
>    resp = msgbox(prompt:="overwrite existing?",buttons:=vbyesno)
>    if resp = vbno then exit sub
> end if
> 
> But those extra commas shouldn't hurt anything.  When you bring open the .csv,
> excel will ignore them.
> 
> I think that this comes close to describing your problem:
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q77295
> XL: Column Delimiters Missing in Spreadsheet Saved as Text
> 
> (It actually describes missing delimiter, but if some are "missing", maybe the
> ones appearing are "extra".)
> 
> (But after you import your .csv file, you'll see that you didn't have to worry
> about this.)
> 
> rpw wrote:
> > 
> > Hi Dave,
> > 
> > Thanks for responding so quickly.
> > 
> > The version idea/problem is one I didn't think about before.  I'm now
> > including the version number in the auto-save file name.  I don't expect too
> > many changes to the layout, but rather to the hidden data - prices and such.
> > 
> > I think also that if the user clicks the import button I'd want the macro to
> > check to see if there is data in the input cells and if there is then ask the
> > user if they'd like to save the existing first.  If not then clear out any
> > existing data and import from a selected csv file.  But those are things I'll
> > have to ponder out.
> > 
> > I have other questions regarding the columns in the csv now.  My existing
> > sheet uses up to 12 columns in some rows and 3 or 4 in other rows.  When I
> > look at the file with "Notebook", some of those rows end with a comma and
> > others don't and I don't know why.  I'm thinking that I might have made a
> > mistake somewhere and should have just a single column.  Do you have any
> > suggestions on where I might find some more info on this kind of thing?
> > 
> > thanks...
> > 
> > "Dave Peterson" wrote:
> > 
> > > Questions in Column A.
> > > Responses in column B.
> > >
> > > Then when you save as .csv, you have essentially two columns.
> > >
> > > Then you could either provide a macro that opens the .csv and copy|paste special
> > > Values from the second column (of the .csv file) into the second column of your
> > > input sheet.
> > >
> > > But be careful.  If you insert new rows (questions), you might be putting the
> > > wrong response with the wrong question.
> > >
> > > I think I'd add a first question:  Version of Input Sheet.
> > >
> > > Then you could build rules that say that question #8 is now question #10, and #8
> > > and #9 will default to a new value.
> > >
> > > So you'd have a conversion for each version (until a version gets old enough to
> > > say that you're not supporting it any more.)
> > >
> > > Good luck,
> > >
> > > rpw wrote:
> > > >
> > > > Hi again,
> > > >
> > > > I've got an idea for the 'export' side of the problem.  I've created another
> > > > worksheet for data collection where each cell equals one of the other
> > > > worksheet's 'input' cells.  I'll then have a 'Save As .csv" routine run so
> > > > the data collection sheet will be saved.  I'm working on building this now
> > > > and I don't think this step will be any problem.
> > > >
> > > > However, I don't quite know how to go about populating the blank template
> > > > from one of the saved csv files.  Any help is appreciated.
> > > >
> > > > Thanks in Advance
> > > >
> > > > "rpw" wrote:
> > > >
> > > > > Hi.  I think this is possible, but I don't know how to go about it or where
> > > > > exactly to look.
> > > > >
> > > > > I have a multi-worksheet workbook that is around 2.4 mb in size.  The user
> > > > > inputs a relatively small amount of information on a few of the worksheets.
> > > > > The rest of the worksheets hold fixed data used for performing various
> > > > > calculations or building reports.  For each separate use, the user is
> > > > > performing a 'Save As'.  I have one user who is creating up to 20 of these
> > > > > files each day which is really filling up their hard-drive.
> > > > >
> > > > > I'd like to have a solution to this problem and I'm guessing that if all of
> > > > > the 'input' cells were to be exported to a comma delimited text file, the
> > > > > text file would be very small in comparison.  Likewise, if the text file were
> > > > > imported, all of the calculations and print functions would still work
> > > > > normally.
> > > > >
> > > > > So, my question is: How do I go about this?
> > > > >
> > > > > I'm sure the solution is VBA, but my VBA knowledge is very slim.
> > > > >
> > > > > Any and all help is much appreciated.  TIA    :-)
> > > > >
> > > > > --
> > > > > rpw
> > >
> > > --
> > >
> > > Dave Peterson
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
rpw (18)
2/4/2005 7:05:04 PM
Hi Gordon,

Well let me see if I can explain this...

This particular computer is not networked and it has an older hard drive 
that isn't measured in 'gigs'.

As far as the spreadsheet, it is a pricing tool.  In the hidden sheets are 
fixed prices for certain basic products/items to which there are then fixed 
multipliers for applying to those prices.  Then there are other accessory 
items with fixed prices.  The user inputs quantities of each of the basic 
products and accessories that are used in up to four different areas of the 
job, then a material is selected which applies a multiplier to the basic 
prices.

This particular user's job is to double-check the (for lack of a better 
term) invoices the salespeople submit with the sold jobs.  Each job needs to 
be saved as a separate file in case the re-pricing needs to be reviewed.

I want the 'master' file with the fixed data and formulas to remain 
unchanged and the variable data for each job to be saved for review.  In the 
spreadsheet's current format, the entire spreadsheet is saved.  I'm trying to 
get things so that only the variable data is saved in a format that 
facilitates easy review.

rpw

"Gordon" wrote:

> raw wrote:
> 
> >I have one user who is creating up to 20 of these 
> > files each day which is really filling up their hard-drive.  
> 
> You can't "legislate" for this. The question has to be "WHY" does the 
> user need to change this file 20 times a day, and WHY can they not 
> either delete some of the old files or save them to a network drive 
> instead? What is so important about the number of times the file is 
> "saved as"?
> 
> 
> --
> Interim Systems and Management Accounting
> Gordon Burgess-Parker
> Director
> www.gbpcomputing.co.uk
> 
0
rpw (18)
2/4/2005 7:29:04 PM
Reply:

Similar Artilces:

Best Practices for reporting in a multicompany environment
I am struggling with the best approach to providing multicompany reports to our Finance, Materials and Product Management departments. 1) Datawarehouse - I will need to have some sort of ETL program to load the warehouse 2) Businessportal - someone will have to build KPI's to consolidate data. 3) FRx - it costs more than I would like to have it used across all companies 4) Smartask - it doesn't work -- we returned the product. 5) Crystal -- do the aggregation within a report 6) Create views within a GP database that combine the same tables from other company databases. I'd l...

Best way to get a pick-list on a form?
Is there a safe and effective way to get a pick list of values for a Standard Outlook form? Maybe a Macro button? What I want is to limit and auto-enter some properties, without having to re-do an entire form. Simple, or no? -- dBaser from back in the day ... It might be simple, but it's hard to say without knowing exactly what you mean by "standard Outlook form" and what business goals you're trying to accomplish, in what version of Outlook. Please provide more details. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Ju...

Best way to deal with contacts and commercial opportunities
Sorry for the misleading title. We are using Exchange Server 2007 and Outlook 2003/2007. I'd like to create company's address book by using a Contact Public Folder, containing all our commercial contacts. I'd like these contacts to be assigned to different categories (geographical area, customer, product types of interest and so on) Any contact may potentially belong to more than one category. At the end I will need to send customized newsletters and promote marketing campaigns, depending on contact's categories. I was looking for Business Contact Manager, but I do...

Team Lead/resource manager Assign Project tasks
Hello, In Project Server 2007, I am looking to have Resource Managers or Team Leads reassign work to people that are not currently on the project team. The idea would be for a project manager to assign a task to a lead/resource manager (or whatever, i.e. Team, role, etc) and then have the person responsible for that team assign the task to a specific person. In looking at this, it appears that a lead can only reassign work to a person that already exists on the project team. Is that the only way this works? -- Thanks, WDR WDR -- Yup, that's how it works. In...

How many ways to copy data?
I realize in Excel there are many ways of doing the same thing, but I'm curious. Just for kicks, can anyone tell me how many ways there are (or that you can think of) to copy data from one cell to another? My co-worker claims there are 4 ways (File/Copy, the ctrl- c/ctrl-v keyboard shortcuts, the copy/paste buttons, and through VBA code). I'm thinking there are more. I know you can use named references to refer to other cells, and in a sense, copy the data from one area to another by refering to the referenced name. I'm sure there are custom functions that could be cre...

Excel Hyperlink: Is there an easier way to follow then double-cli.
Excel Hyperlink: Is there an easier way to follow an Excel Hyperlink then with mouse double-click? My old version of Excel all you had to do was hit "Enter" In xl2002, I only have to use a single click. So that saves some time <bg>. This has been asked before and it looks like just selecting the cell and hitting enter is gone. Bob Small wrote: > > Excel Hyperlink: Is there an easier way to follow an Excel Hyperlink then > with mouse double-click? My old version of Excel all you had to do was hit > "Enter" -- Dave Peterson ...

Easiest Way to Move Column
I know I have seen this answer before, but I can't seem to locate it this time. What is the easiest way to move a column of data to a different column in one step, without overwriting any other columns, and without having to first insert a column, and without having to go back to delete the original column? Thanks, Carroll Rinehart Hi Carroll move your cursor under the column heading of the column you want to move and hold down the RIGHT mouse button ... drag the column to its new location and let go - you should get the option of SHIFT RIGHT AND MOVE .. Cheers JulieD "Car...

Calculating Date difference in 2 ways
Hi, If in cell A1 I write -- = today() - date(2004,12,31) then the answer I get is a number ( let's say 14 or 15) But if in cell B1 If i write -- = C1 - D1 where C1 -- = Today() and D1 -- = date(2004,12,31), the answer I get is in date format (in excel's 1900 base year) Why is there a diference in formatting of results between the above 2 approaches. Regards, Hari India Hi Jim and Aladin, I have not written my initial query properly. My doubt is that inspite of the same formulas and with both A1 and B1 having no previous formating, what causes excel to interpret these 2 approac...

What is the simplest way to print multiple worksheets?
I have a workbook with 30 worksheets of charts and 40 worksheets of data that feed the charts. Now I like to find the easiest way to print all 30 charts from this workbook at one simple command without printing the 40 data worksheets. Yes, I know I can click all the tabs while holding down the Ctrl key and then print. But I still have to click 30 tabs!!! Is it possible to set the print area across the 30 worksheets that contain the charts? By the way, the print set up for each chart is exactly the same. Any help will be very much appreciated. If your charts are all on CHART sheets in...

simple way to do a server-side autoreply in E2K ?
Hi all....looking for a little advice once again =/ I'm looking for a simple way to make E2K reply with a blurb anytime email is sent to a particular email address. I would prefer not to have to involve Outlook with some client-side rule.....was just hoping for a way to make E2K kick off an autoreply. So far, the only thing I've done in Global Settings | Internet Message Format | Advanced Properties of Default, is enabled Automatic Replies. Some of the posts mentioned using event sinks, which I know nothing about. Any ideas? Thanks in advance! Robert Robert wrote: > Hi all.....

Is there a way via GPO to remove the "Reply to All" option on the Standard Menu but keep in the Drop Down
I am looking for a way via Group Policy to remove the "Reply to All" button in Outloook 2000 and 2003 but keep the option active in the Drop Down menu (Actions/Reply to All). I've tested via Sue Mosher's solution using GPO but the Control ID 355 ties both functionality together. Is there a way to do the above via policy? Thanks Ariel magic1_aad@yahoo.com No, there is no way to do that with Group policy. Why would you want to = remove it in one place but not in all?=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Adm...

LONG
Hi all, I have a .PST file I've been moving from system to system over the years that I use primarily to keep notes, contacts, appointments, task lists, etc. I do NOT use this PST file to store emails, so I end up with a useless "Inbox" entry that I can't get rid of in the main tree, but that's a non-issue. Typically, on all my machines, I move the entire "My Documents" folder from its default location so it's got its own partition (J: drive), which contains a "PSTs" folder. So, "My Documents\PSTs" is actually "J:\PSTs"...

Easiest way to create forms with Excel?
Hi all - apologies if this is a FAQ question, was unable to find good answers there. My organization often needs to distribute various 'forms' designed in Excel. People will fill them in, (often print a copy and) send the filled-in sheets back to us. There are a lot of problems with this: people will modify stuff where they're not supposed to, will leave 'mandatory' options blank, and the process of copying/pasting data for further processing once we get the filled in sheet is laborious. For various reasons we need to stay with Excel (i.e. Access, or redoing everything a...

Best way to measure investment performance?
I'm using MS Money Premium 06 and wonder what the best way is to measure quarterly and annual investment performance, given that cash flows in/out and stocks are bought/sold during the period being measured. There are several Portfolio views that provide a general total return for 3 and 12 months but it doesn't look like it takes into account the above issues. Perhaps a Report is more appropriate? Any assistance is appreciated. Thanks, Miles In microsoft.public.money, Miles wrote: >I'm using MS Money Premium 06 and wonder what the best way is to measure >quarterly...

is there a way to do this automatically
is there a way to do this automatically setting prices a, b, c Hi Victor, Have you tried Mihir's suggestion? See his reply to your original post. This should work for you, if you are unsure of how to do it reply to his message he left in your original thread. And don't forget to backup your data first :) hth kevin "Victor" wrote: > is there a way to do this automatically setting prices a, b, c ...

any way to combine registers so z-out contains all?
Is there any way to combine all registers so that one z- out would contain all data from days work on different registers? We would like to be able to use one actual cash register, but be able to sell from multiple registers or work stations. Is this possible? As far as I know and understand you must z out each individual register. We have 3 machines that can do the register but use 1 everyday. The other 2 machines we z out after they are used for sales. So one might have a batch from 4/30/04 to 7/1/04 if it wasn't used for sales during that period. -- Elizabeth M. "JackT&qu...

Easy way to delete names in a workbook?
I inherited a workbook. The guy defined about 50 names, referring to a bunch of other workbooks. Is there an easy macro to delete all defined names rather than doing it one by one? Try Jan Karel Pieterse's Name Manager utility. It's a free add-in that you can download here: http://www.bmsltd.co.uk/MVP/Default.htm Joe wrote: > I inherited a workbook. The guy defined about 50 names, > referring to a bunch of other workbooks. Is there an easy > macro to delete all defined names rather than doing it one > by one? -- Debra Dalgleish Excel FAQ, Tips & Book...

Correct way to link IV30300 & IV30500
I'm trying to build a Smartlist that combines the data from IV30200, IV30300 & IV30500. I;m trying to reproduce the canned smartlist Inventory Transactions, but with the notes attached. IV30200 & IV30300 are a no brainer, but I can't come up with a combination of columns that link IV30300 and IV30500 in a many-to-one relationship. I seem to be getting cross-join-type results. Thots? Ken, Are you sure IV30500 is that table you want for notes? Unless I am missing something, there are no notes in the IV30500 table, just GL distributions. Can you clarify specifi...

Easiest way to copy a single record from VB?
Hi All, Assume I have a recordset handy, and I'm looking at a record I want to copy. What is the simplest way? In the past, I've used AddNew, then copied contents field by field. Is there a better way? Thanks, Max You can highlight the entire record by clicking to the left of the first field. Copy the entire record. Then go to the end of the recordset and paste the enitre record in the last row (last row will always be blank) "Max Moor" wrote: > Hi All, > > Assume I have a recordset handy, and I'm looking at a record I want to > co...

Easiest way to "transpose" data?
I have a table, laid out as follows: Acct, Year, period1, period2, period3, period4,...through period13 Sales 2006 $1000 $2500 $3100 $1250 What is the easiest way to return each record as 12 records, Acct Year Period, Amount Sales 2006 Period1 $1000 Sales 2006 Period2 $2500 Sales 2006 Period3 $3100 Sales 2006 Period4 $1250 I can't mess with the table structure, and I actually have a bunch of different tables I will need to view in a similar fashion. I can figure ways to do it, but I am sure some of the genius level talent here has an easy way to do this? Phil If we were ...

Return calcs in Portfolio Manager way off after transfering in sec
I recently transfered some stock positions from an E-Trade account to my Fidelity account. The history of puchases is correct, but the return calculations are wacko. One loser shows an anualized return of a 100% loss. Under investment information, only the transfer is shown. But, under "see investment activities", the various purchases are shown. What is wrong and how can I fix it? ....Rich I have a similar problem. My Cash Reserves account shows a 3 month return of 6% (no way) and a YTD return of 0.4% (probably correct). Yet the downloaded cash movements and dividend pay...

Question File Sharing Only Working One Way
The problem I'm experiencing is an inability to open or save Microsoft Office files (Word and Excel files, in particular) while operating on a very small and simple home office network. We have a desktop system running Windows 7 as well as Microsoft Office 2007. We also have an iMac running Microsoft Office 2008. That is the full extent of our computer network (connected through Apple's Time Capsule router). We of course have file sharing turned on for both computers. I have no problems in opening or saving files when I'm operating on the iMac: I can open and s...

Any way to customize asset allocation categories in Money 2004?
I want to be able to assign my own asset allocation categories to Microsoft Money to be able to group by them. For instance, Money would view a precious metals mutual fund as only a "mutual fund", but for me, it's really a separate investment class I want categorized under "precious metals". I'm pretty sure these categories aren't editable, so I was wondering if anyone has found a workaround (besides creating a separate Excel spreadsheet, which I already did). Thanks. Many of us wish for it. Instead we get Essential * and turn to Excel. <matty_lewis@yah...

Is there a way in Exchange 2000 to export a list of all the mailboxes over a certain size?
Is there a way in Exchange 2000 to export a list of all the mailboxes over a certain size? At the moment I am just going in Exchange System Manager and then trawling through all the storage groups to check the numbers. It is kind of laborious to check the size of mailboxes and the number of messages one at a time, so was wondering if there is a way to do this automatically within Exchange, or do I need to use third party tools? Thanks, QH This is possible if you have Crystal. There is a 3rd party, will have a poke around now Yes it is possible to get a list of mailboxes over a certain s...

Any way to pretty this up?
I am trying to copy data from "Engine Data" and paste it into a new sheet. Engine Data contains many columns of FG_HC... I only want the one under the [Hertz] heading. The format of the Engine Data looks like the following: [Mode] FG_NOX FG_HC FG_CO data data data [Hertz] FG_NOX FG_HC FG_CO data data data etc Here is my code: Sheets("Engine Data").Select Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ...