linking excel and access

grapegrower

New Member
Joined
Sep 15, 2002
Messages
28
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
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

New Member
Joined
Sep 15, 2002
Messages
28
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 15, 2002
Messages
28

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 15, 2002
Messages
28

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 15, 2002
Messages
28
MS Fertigation Solver Mr.Excel help.xls
ABCDEFGH
1WholeAreaHectareNFertilizer
21.5CalciumNitrate
315.50%
4WeeklyFertilizerRequirement6.4516129
5WeeksNPKMg
611.190.711.790.3611.52kg
721.520.912.280.4614.68kg
832.151.293.230.6520.85kg
943.482.095.221.0433.66kg
1054.822.897.231.4546.66kg
1166.173.709.251.8559.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
ABCD
1ManufactureBagName%NitrogenConversionratio
2PivotCalciumNitrate15.56.4516129
3PivotUrea462.173913
4
5
6
Nitrogen



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

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Forum statistics

Threads
1,143,614
Messages
5,719,719
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top