linking excel and access

[grapegrower]

Hi,
I'm new to using excel for more than just a spreadsheet so bear with me please. I want to store a set of constants in access and use them in excel in calculation. I would like to have access to the same database file from more than one excel file. I was thinking of using combo boxes if they can be linked. What is the best and simplist method to use. Hope this is clear.

 

[WillR]

Welcome to the board....

Just how many constants do you want to refer to in your access database?

Please giva an example of the sort of calculations you are trying to do once you've got the values from access... a fuller description of your data etc would be helpful

 

[grapegrower]

Hi Will,
I think a spelling error has caused me to be a little misleading. I am trying automate the calculation of the weekly fertilizer requirement of my grape varieties depending on variety and ponential yield. I have done this in a worksheet which gives me the requirement of actual Nitrogen, Potassium etc for the season.
I apply the required nutrient through commercial fertilizers which have varing amounts of N or P or K or Mg or what ever.
I am thinking of compling a database of all avaiable fertilizers and their percentage of each nutrient, and assign a constant that you have to multiple the actual requirement by to give you the amount of commercial fertilizer needed to match the requirement.
I was thinking that I could make a link in the workbook with the database so I can have access to the list of fertilizers that have the nutrient I require and also the constant for the calculation.
I don't need to have fertilizers in a database, it may be easier use a workbook and have a sheet for each nutreint. Then if you call up sheet1 it has all the nitrogen based fertilizers, the %N, and my constant.
I hope I havent bored you, any suggestions would be greatly appreciated.
Paul

 

[PaddyD]

swap help for wine!

1) If you're not likely to have more than some thousands of records about fertiliser NPK contents, and are more comfortable with excel, stick with it - access wont offer any obvious advantages with 'small' datasets
2) The formulas for calculating the fertiliser requirements should be fairly straightforward, but we'll need to know a bit more about how your data is set up
3) If you're relatively new to using excel to build relatively 'large' applications / solutions, then have a look at the links here:

http://mrexcel.com/board/viewtopic.php?topic=20119&forum=2

for some good general remarks.


Post back with more info...


Paddy

 

[grapegrower]

Hi,
sorry to disapoint but the grapes are table or eating varieties not wine but there could always be a good aussie wine on the offer.
The data in terms of generating the actual nutrient requirement are derived from a trend line where I have known desired nutrient levels at certain growth stages. I have "forcast" the values between the known points believing the change is linear.
The data in terms of the fertilizers is from print outs and I manually change the cell equation when I change fertilizer. This has worked in the past but now I find that I'm changing fertilizer more times during the season as I fine tune the spreadsheet and I've got bits of paper all over my desk.
So I can set up the data to what ever is easier to link. At the momment I have the columns, manufacture, name , %NPK and Mg and the constant after each (10 columns and enter as rows)
thanks for the links Paddy I will check them out
Paul
This message was edited by grapegrower on 2002-09-18 05:56

 

[PaddyD]

OK - how about downloading the html maker addin from the link below this site & posting up a snapshot of the data you have. (don't post anything with too much formatting - a representative example will do). Give more details on the formula you are currently using. work through an example of what you are after - start data, expected outcome etc. Basically, post back with more details on what you've currently got, where you want to get to etc.

Paddy

 

[grapegrower]

Hi,
I downloaded the addin and will write a post offline over the weekend, for some strange reason I thought this was going to be easy.
Paul
This message was edited by grapegrower on 2002-09-19 14:53

 

[PaddyD]

Hi - I'm sure it will be fairly straightforward! It's just that we don't yet know what things like "assign a constant that you have to multiple the actual requirement by to give you the amount of commercial fertilizer needed to match the requirement", or at least not in enough detail to suggest solutions! I'm sure it will come together once we've seen the data etc.

Enjoy the birthday party!

Paddy

 

[grapegrower]

MS Fertigation Solver Mr.Excel help.xls
	A	B	C	D	E	F	G	H
1	WholeAreaHectare						NFertilizer
2	1.5						CalciumNitrate
3							15.50%
4		WeeklyFertilizerRequirement					6.4516129
5	Weeks	N	P	K	Mg
6	1	1.19	0.71	1.79	0.36		11.52kg
7	2	1.52	0.91	2.28	0.46		14.68kg
8	3	2.15	1.29	3.23	0.65		20.85kg
9	4	3.48	2.09	5.22	1.04		33.66kg
10	5	4.82	2.89	7.23	1.45		46.66kg
11	6	6.17	3.70	9.25	1.85		59.67kg
Irrigation

This is the weekly requirement sheet I use.

I would like a way to select a fertiliser eg cal' nitrate from a dropdown list or such like in G2 from another source in a way that the %n is also displayed in cell G3 in this case. This example has 1 fertiliser but I use a combination of 3-5 as a minimum

As I type this I can see that it would be easier to include the constant as part of the question in G6:G11 as 100/$G$3

the list of fertliser information can be setup as required at the moment its like this

Stock Fertilizer.xls
	A	B	C	D
1	Manufacture	BagName	%Nitrogen	Conversionratio
2	Pivot	CalciumNitrate	15.5	6.4516129
3	Pivot	Urea	46	2.173913
4
5
6
Nitrogen

any suggestions are welcomed
Paul
This message was edited by grapegrower on 2002-09-22 04:05

 

[PaddyD]

OK, see is this helps:

what we'll do it set up a data validation list so you can choose your option for G2, then use a vlookup to return the corresponing % in G3 - no typing needed.


1) On your nitrogen sheet, go to insert | name | define.
2) Type Chemicals in the 'Names in workbook' box. Set the name to refer to
=OFFSET(NITROGEN!$B$1,0,0,COUNTA(NITROGEN!B:B)-1)
& press enter.
This will give us a dynamic named range that will grow as you add more entries to the NITROGEN sheet. The formula defines a range of cells that starts in B1, and goes down as many cells as there are items in column B:B. The -1 at the end is to compensate for the column heading. (It is imprtant not to have gaps within your data in col B).
3) On the IRRIGATION sheet, select G2 & go data | validation
4) set Allow to 'List". Set the cource to
=Chemicals

We now have a drop down list that (a) picks up values from your nitrogen sheet and (b) will grow etc as the list changes.

5) On the NITROGEN sheet, go to Insert | name | define again.
6) Set up another named range called LTable (for 'Lookup Table'). Set it to refer to:

=OFFSET(NITROGEN!$B$1,0,,COUNTA(NIRTOGEN!B:B)-1,2)

This will give us a dynamic named range that will (a) grow as the list changes, and (b) refer to the data in both cols b & c.

7) In cell G# on the IRRIGATION sheet, enter:

=vlookup(g2,NITROGEN!LTable,2,FALSE)



All going well, all this should end up returning the right %s for entries you pick from the list in G2 & should not require further maintenance as your data changes.

There are ways to further refine this. We could, for example, make the entries in the drop down in G2 depend on another cell - for example, we could set up a similar drop down for Manufacturer first...

Anyway, I hope I've understood what you wanted - see how you get on & post back.

Paddy