• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

Excel help, please.

kookbreaker

Evil Fokker
kookbreaker
Joined
Aug 23, 2001
Messages
15,888
Hi folks. Here is what I am trying to do.

I have a bunch of excel files, they are master lists of products. The prices on them are determined from several of the columns on the book. For example, column 'A' determines the base price, b determines if it goes up a bit, C may make it go down, and d may make it go up a bit. This gives us a price in column 'E'. Needless to say, column 'E' is a complicated formula.

Now what I need to do is take the figures in column 'E' and make what is there the actual entry. I am making a new workbook for online price adjustment and they need an excel file without all these extra columns (A, B, C, and D will mess up the database). Is there some feature of Excel where I can select a column, and the price was determined by the formula now becomes a fixed entry?

Thanks in advance. :D
 
I could be wrong but I believe that you highlight and copy the column in question. Where you want the values you right click and choose 'paste special'. This gives you a dialogue that has the option to paste the values only.
 
jim... that's exactly right.

...

Meanwhile, I'm trying to get a semi-transparent fill with a 3D area chart. gngngn... :mad: It's a formatting feature unavailable in 3D charts. I'm currently working on a scheme to export images and copy semi-transparent images over top of each other... *sigh*
 
jimlintott said:
I could be wrong but I believe that you highlight and copy the column in question. Where you want the values you right click and choose 'paste special'. This gives you a dialogue that has the option to paste the values only.
Click to expand...

Yep, Copy>Paste Special is on of the most useful Excel functions to learn.
 
a_unique_person said:
That's what I always wonder, why use Excel when you can use a database. Excel is great for a once off quick and dirty, but anything enduring should be in a database.
Click to expand...


In my job I use databases all the time. I'm not an expert on db's and find myself muddeling through a lot of the time. I have taken some courses on Access and I am for the most part self taught. Working for a small community, I am the DB expert. I have found tha the 2 most common reasons that people won't use a database and use Excel are:

1. People don't have access to a database. Most of the people I work with have Office standard and don't have Access.

2. No training for those who have Access available to them. Almost to a person, those who have Access think it's another spreadsheet program and "it doesn't work right."

I tried to explain to someone once the advantages of Acess and the limitations of Excel and I was sharply told ''I used Access once and it wouldn't do the formulas I type in the cells. That's why I use Excel, so don't tell me how to use Excel, I know Excel."
 
Not knowing enough about databases, I stayed with what I know.

Also, from my limited experience in trying to work with a database, I consider them crash-city.

No thanks.
 
Excel is useful for ad-hoc analysis, or if you need a good deal of flexibility.

Access is more useful if you desire automation, or if there's so much data that Excel chokes on it.
 
kookbreaker said:
Not knowing enough about databases, I stayed with what I know.

Also, from my limited experience in trying to work with a database, I consider them crash-city.

No thanks.
Click to expand...

Access isn't so bad, now that it's matured. The old days of DBase whatever are long gone.

If you want a real database, SQL Express is free, and has the full integrity of SQL Server.
 
a_unique_person said:
Access isn't so bad, now that it's matured. The old days of DBase whatever are long gone.

If you want a real database, SQL Express is free, and has the full integrity of SQL Server.
Click to expand...

Among other things People like access because it's file based. They know how to give an access database to a client, they know how to make a copy of the database, etc....

SQL Express (and the older MSDE) requires a new way of thinking from files. It requires a special machine that all users connect to. Backing up a database requires special software. Giving the database to someone else involves all kinds of work.

I think SQLite is actually pretty good substitute for Access since it doesn't have a server component and stores everything in a single file. It doesn't have access' forms and VBA, but you generally want those seperate from a database anyway.

eta: http://www.sqlite.org/
 
a_unique_person said:
Access isn't so bad, now that it's matured. The old days of DBase whatever are long gone.

If you want a real database, SQL Express is free, and has the full integrity of SQL Server.
Click to expand...
Do you use it? I could use some assistance trying to get a few simple things done with it...
 
a_unique_person said:
What's the problem?
Click to expand...

Couple things I can't figure out:

1. How to import data
2. How to store images in a table. I did some research and the best answer I could find was, "Don't, use hyperlinks instead." ... which is fine, except I couldn't quite tell how to do that, and especially how to make the hyperlinked image appear embedded into a form.
 
I find that Excel is OK for basic calculations and data analysis, but not very good when it comes to manipulating that data. Also, unless you want to learn visual basic for applications, the macro recording system is fiddly for anything other than simple procedures.

I think Access is a laugh. With it, you can knock up a fairly professional looking application quite quickly, with all sorts of bells and whistles that make clients go ooh! If you don't want to learn VBA, the macro builder makes it simple.

It's unstable, mind you. Especially with multiple users. I'm using php/mySQL for big multi-user stuff.
 
gnome said:
Couple things I can't figure out:
1. How to import data
Click to expand...

Some info here:
http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

2. How to store images in a table. I did some research and the best answer I could find was, "Don't, use hyperlinks instead." ... which is fine, except I couldn't quite tell how to do that, and especially how to make the hyperlinked image appear embedded into a form.
Click to expand...

Yeah, I'm not a fan of large binary objects in a database. I think the method for making the image appear on a form is pretty dependent on what you're writing your form with. Most of my database/form experience is with web browsers, so I would just store the http:// form url, retrieve and insert it into an img src= control.

For access or other active x thingys. Hmmm, been a while, but I thought one of the properties of an image box was the image it displayed for the background and that you could give it a path to the image. So a UNC is probably what I would store in the database.
 
TheBoyPaj said:
I find that Excel is OK for basic calculations and data analysis, but not very good when it comes to manipulating that data. Also, unless you want to learn visual basic for applications, the macro recording system is fiddly for anything other than simple procedures.

I think Access is a laugh. With it, you can knock up a fairly professional looking application quite quickly, with all sorts of bells and whistles that make clients go ooh! If you don't want to learn VBA, the macro builder makes it simple.

It's unstable, mind you. Especially with multiple users. I'm using php/mySQL for big multi-user stuff.
Click to expand...

Access is great single user, don't try to make it do more than it can.
 
You must log in or register to reply here.

Back
Top Bottom