Any Excel Gurus out there?

[Peter]

I'm trying to set up all of my field records in excel with each field a seperate worksheet, each farm a workbook and so on.  What I was wondering was if I could set up a master workbook with all the values (disking, planting, fertilizer costs, etc) and assign variables to them that i could then reference on each individual worksheet.  That way i always know how much i have invested in any given crop.  

I will also make projections easier

is there any way of referencing between sheets in a workbook, referencing between diff workbooks, or assigning a word variable to a cell with a numerical value in it?

if there is any lack of clarity in this mess, please let me know, i'll try to illustrate it better.

I tried mirosoft "help"  but it wasn't very helpful

thanks,
Peter

[kelly]

[MamaMoose]

Hi Parker,
Yes there is a way. I haven't tried it for quite a while so I will check back with you when I refresh my memory on the right procedure. I do remeber about having to worksheets open and copying an item from W1 and the pasting it into a cell in W2. Excell automatically takes care of the linking. Also if W1 is closed and you try to access a linked cell in W2, it will ask you if it;s OK to link to the closed worksheet.

Check in the Excel help file for "linking worksheets". I will try to go back tonite and make sure I gave you the correct scoop  above.

MamaMoose

[Parker]

Parker is down here =]
but would like this information as well

[Xairbusdriver]

Too bad you don't have FileMaker. What you are trying to do is build a database out of a spreadsheet. A task often filled with frustration, disappointment and lots of work. You don't try to disc a field with your combine, you get a tractor suited to the task. Your time is probably worth more than the value of any one crop you'll plant!

You might be able to find one of these 'systems' already built but whether it fits your needs is another problem. Searching for Excel forums might save some time. Good luck! And good weather!

[Peter]

These systems do exist, but many of them are clumsy and don't completely address what I'm trying to do.  I have experimented with Filemaker, but just a demo version of it.

I will have to look on the M$ website and see what kind of forums I can find.

Thanks,
Peter

[Shades of Gray]

QUOTE

I'm trying to set up all of my field records in excel with each field a seperate worksheet, each farm a workbook and so on. What I was wondering was if I could set up a master workbook with all the values (disking, planting, fertilizer costs, etc) and assign variables to them that i could then reference on each individual worksheet. That way i always know how much i have invested in any given crop.

I will also make projections easier

is there any way of referencing between sheets in a workbook, referencing between diff workbooks, or assigning a word variable to a cell with a numerical value in it?

I use Excel every day, 8-10 hours. So just about anything you want to do, it can be done.

A few questions about set up. Are you wanting those values all on one worksheet or separate worksheets, but same workbook? How are you getting values into Excel? By hand?

Will the size change; that is, will you keep adding rows and/or columns?


It sounds like named ranges will work fine for you. Named ranges can be specific to a worksheet or a global name (for the entire workbook). Also, you can set up static named ranges or dynamic named ranges, particularly if you will be adding new rows/columns of data.

NOTE: For worksheets, never use spaces for the worksheet names; if you want multiple words, then use capital letters for each word with no spaces, i.e. MyData. And for named rangeds, Excel will not let you have spaces for them.

Let's say you have values for planting on a worksheet named "Plantings". And assume your data is in the range A1:G25, but you may have new columns or new rows. Then while on worksheet Plantings, go to Insert > Name > Define. In the top portion, type in a name for the range. I tend to use Data plus a suffix to identify it. In this case, type in DataPlant. Then in the bottom box, type in the following formula:

=OFFSET(Plantings!$A$1,0,0,COUNTA(Plantings!$A:$A),COUNTA(Plantings!$1:$1))

Click OK.

To explain, the OFFSET function has five arguments. The first is the starting cell, in this case Plantings!$A$1. Then the number of rows to offset, in this case 0; then number of columns to offset, in this case 0. The fourth one is a COUNTA function which counts the number of used rows in column A, and the fifth counts the number of columns in row 1.

Now, if you have three columns and 12 rows, this named range will identify it. If you have 12 columns and 45 rows, it will still refer to the entire range.

Then anywhere else in the workbook, if you want to refer to this range, then you can use the name DataPlant. Also, if you want to pull data from any of these ranges, then you can use four different functions or combinations: VLOOKUP, HLOOKUP, INDEX/MATCH (this last combination is the most powerful). And you can use conditional sums. For one condition, use SUMIF, for multiple conditions use SUMPRODUCT.

Now, if this is the direction you want to go, then you will have to provide more info.

[Gregg]

Hey, that was pretty meaningful dialog!

Great, specific advice. Peter will be grateful.

Love that signature.

[Peter]

Thnaks!!

I have been playing around, and i think i have it working for the most part.  I was naming individaul cells instead of a range, but it seems to be a similar concept.

We have a M$ genius in school who does a lot (a LOT) of excel, but he was out last week.  I'm going to contact him on monday and see if he has any further advice.

As I have it set up now, I have one sheet in the very back of the workbook that is dedicated to "reference values", the cost of planting, tillage, harvest, etc.  each cell is a different function and has a name assigned.  from there  can refernce the names in other pages and the value show up.

it works a lot better than calculating everythin outside of the computer then entering it into a appleworks text ducument.

Thanks again,
peter

[Shades of Gray]

Good to know that you have been experimenting with it. I learned a lot by doing things on my own, and discovering that sometimes I made mistakes, other times I was using convoluted approaches, and sometimes I did alright. But that is learning.

The only problem with naming individual cells is that there is an upper limit to how many named ranges can be in a workbook.

Let me know if you have any more questions.

[Peter]

thanks, it seems to be doing what i need it to now, but i'm trying to add soem more data to it so that it can be a one sheet record keeping system for both the farm and for organic certification

I may run into problems, but hopefully not

thanks,
Peter

[Parker]

AppleWorks will do just fine
i actually like it better than filemaker
and its cheaper too =]

[Peter]

OK, up against something i can't solve.  how can i make a range of data into a pull down menu in one of the cells?

i've seen it done, but don't know how to do it

thanks,
Peter Martens

[Shades of Gray]

Requires two steps (to allow cross-worksheet use).

1. Define the range: Let's say it is MyData, and it refers to Sheet1!$B$2:$B10 Click OK.

2. Go to the cell where you want the drodown, then on the menubar go to Data > Validation. In the first tab (Settings), there is a dropdown, and should have "Any value". Click on that dropdown and choose "List". Then in the box below that type in the following:

=MyData

Click OK. And now, you have a dropdown in the cell.

If you want the named range to be adjustable to allow for more or fewer items, then follow the advice above for a dynamic named range.

[Peter]

OK, thanks, I'll try that tom.

I'm goin to bed now.  let it snow and blow and be frigidly cold outside, i've had enough



-Peter