| Author |
Topic: excell 07 users need help |
Bill Myrick
From:
Pea Ridge, Ar. (deceased)
|
Posted 9 Jun 2009 4:19 am |
|
| Is there a way to refer to another spreadsheet and cell to import a figure to the sheet I am working on ? In other words a price sheet of lumber that I could manually change when needed and all other spread sheets using that could refer to it for the currnt price ? Would that be called "looking up" maybe ? thanks, Bill |
|
|
|
Jon Moen
From:
Canada
|
Posted 9 Jun 2009 4:51 am
|
|
The easiest way to do that is to copy the cell you want to "look up". Then go to the workbook cell where you want the info, choose Edit/Paste Special, and select "Paste Link". The source workbook doesn't have to be open for the info to be read.
When you open the workbook with the link, you will be asked if you want the links to be updated.
In the formula bar you will see what is involved. You can put them in manually if you want. |
|
|
|
Gary Dunn
From:
near Camel City, NC
|
Posted 9 Jun 2009 4:59 am
|
|
Yes there is; you have to use a command related to special linking (updating). What it does is when you update the base sheet, all other MS related products will automatically update that are related to the specific field(s). I have been using a MAC for quite some time now-so I do not remember the exact procedure.
google MS Excel special linking
g |
|
|
|
Bill Myrick
From:
Pea Ridge, Ar. (deceased)
|
Posted 9 Jun 2009 9:59 am
|
|
| Gary--That sounds like what I need. For instance I will have one work sheet with all the different lumber prices and then the other sheets will be work sheets that refer to it for the current prices of that particular piece it uses. It also envolves roofing, siding and trim as well as other sheets with various doors windows, etc. I have contacted a local college to see if they have any students who wants to pick up a buck or two, maybe that will turn up some help. Thanks guys, Bill. |
|
|
|
Gary Dunn
From:
near Camel City, NC
|
|
|
|
Jon Moen
From:
Canada
|
|
|
|
Bill Myrick
From:
Pea Ridge, Ar. (deceased)
|
Posted 9 Jun 2009 12:27 pm
|
|
| Thanks, Jon--Looking into it now. Bill. |
|
|
|
Bill Myrick
From:
Pea Ridge, Ar. (deceased)
|
Posted 11 Jun 2009 3:18 am
|
|
Gary : Yes I do own the business - www.amkobldgs.com - We have quick books for our record keeping. What I am working on now is a way to figure building estimations like we build here.
Jon, I think I have your idea working, thank you !!  |
|
|
|
John Cipriano
From:
San Francisco
|
Posted 15 Jun 2009 8:36 pm
|
|
I'd use VLOOKUP if you have a whole column or series of columns that you need to populate.
But for a single cell: just type an equals sign, switch to your other spreadsheet, click on the cell you want to reference, and hit enter. Easy peasy.
In case you want the VLOOKUP info:
http://www.youtube.com/watch?v=2wHtcct7mCE
Don't forget the F4 key!
_________________
MSA Semi-Classic S10 w/ 4P+4L and some shiny new tuners |
|
|
|
Bill Myrick
From:
Pea Ridge, Ar. (deceased)
|
Posted 16 Jun 2009 2:30 am
|
|
Is this forum great or what ???? -  Thanks, John for that great video ; it was very gracious of you to share it ! You guys are way over my head but very very helpful, thank you so much !! Bill. |
|
|
|
John Cipriano
From:
San Francisco
|
Posted 16 Jun 2009 5:29 pm
|
|
Oh I didn't record that video
It was just on YouTube. Also it's just in case you want to learn VLOOKUP, which is something that someone showed me a couple of years ago that comes in handy all the time.
But like I said to link one cell to another you usually just type an equals sign and click on the other cell. It won't work if the cell has a weird formatting. But for a normal cell it will work.
Another tip: if you want to actually put an equals sign, or a plus or minus sign in the front of a cell without it becoming a formula, type an apostrophe first. It will be in the cell but it won't show or print.
Excel might be simultaneously the best and worst thing Microsoft's ever made. |
|
|
|
Jon Moen
From:
Canada
|
Posted 16 Jun 2009 8:22 pm
|
|
Bill,
My wife used to work out of town a lot. While she was away I spent hundreds of hours making spreadsheets for work that do anything I could ever want them to do, using formulas and Visual Basic programming and API calls if needed. The key point is to figure out what your goal actually is (not as easy as you might think). Then determine the most clear way to get what you and any other users need.
You mentioned building supplies. That is the industry I am in. Sales of siding, roofing and insulation. I could attempt to give you a little help if you have some specific questions.
Vlookup and Hlookup are useful when you want to get data from a table or range. You have to tell the formula the area to search, which can be cell and column references or you can create a named range. You then have to determine the offset from where it finds the object you searched for.
In an external sheet that you want to have as a database but not open all the time, linking is the way to go. It is a simple copy and paste special, paste link.
I have one file that has links to hundreds of cells in approx 20 files.
Using the equals sign is good for work in the same file and will work as long as the cells have the same formatting. (you can ensure that by entering the formula i.e.: "=A7" in A5, copying A7 and Paste Special/Paste Formats into A5.
Vlookup can be a little unusual. Depending how you set the parameters, it may find the first close but not exact match .You also have to be careful what order the information is in.
Only read this if you understand Vlookup. This is from the Excel 2003 help.
One of the parameters for Vlookup is Range_lookup. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.
If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned. |
|
|
|
Bill Myrick
From:
Pea Ridge, Ar. (deceased)
|
Posted 18 Jun 2009 1:57 am
|
|
| Again, thanks so much. I am saving all this info and might even recruit some local tech help with it. What my goal is---is to be able to refer to a given section of a building and have the program go back to another sheet which shows the various materials and quanities and then have that refer to the other sheets which show the prices of the various lumber and metal parts. When a price sheet is updated , it will automatically flow through the other pages. |
|
|
|
Jon Moen
From:
Canada
|
Posted 18 Jun 2009 5:32 am
|
|
| Bill, That will work just fine. The links will update as you want. Depending on your settings, you will be asked if you want to update the links. |
|
|
|
John Cipriano
From:
San Francisco
|
Posted 20 Jun 2009 10:58 pm
|
|
| Jon Moen wrote: |
| The key point is to figure out what your goal actually is (not as easy as you might think). |
That gets truer for me every day. And it reminds me of this:
And this old chestnut:
| Quote: |
| On two occasions I have been asked, 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. |
I can't say I've ever used VLOOKUP with the last option set to TRUE. Don't see the point.
I hope by the time I need to do API calls in Excel I'll have learned a proper language well enough to do them in that been hacking a little in Python... |
|
|
|
