Jim wrote:
I have a two worksheet excel program, and it is being designed as a
sort of commodities "inventory net worth" program.

First sheet (Daily Spot Prices) has columns containing date,
commodity 1, commododity2, commodity3...... etc, each price
representing price per pound in dollars. These prices change daily, a
new row is used each day. Each column item is given a name, let's
say Corn, Wheat, Soybeans, etc.

Second sheet, has complete inventory of items, that is, item number,
number of pounds, original cost, TODAY's VALUE.

On first day, for Corn, the value of that item's formula would be:
=100*CORN (for say 100 pounds of corn). CORN is defined on
first sheet using NAME, INSERT, DEFINE, CORN='Daily Spot Prices'!$B$1,
WHEAT='Daily Spot Prices'!$C$1, SOYBEANS='Daily Spot Prices'!$D$1,
so much for day 1.

For Day 2, the NAME would be: CORN='Daily Spot Prices'!$B$2,
WHEAT='Daily Spot Prices'!$C$2, SOYBEANS='Daily Spot Prices'!$D$2,
thus day 2.

Now, each day I start a new row, Column 1 is date, ColumnB is CORN
spot price, Column C is WHEAT spot price and Column D is SOYBEAN spot
price, and so on.

Now, each day after entering date/spot prices in the next row, I have
to open INSERT, NAME, DEFINE and manually go in and change the row
number in each of the definitions to have the spreadsheet refrence the
day's price and apply formula to current price.


Hi Jim,

I think this should solve your problem of defining dynamic names:

CORN=OFFSET('Daily Spot Prices'!$B$1,COUNTA('Daily Spot
Prices'!$B:$B),0,1,1)

WHEAT=OFFSET('Daily Spot Prices'!$C$1,COUNTA('Daily Spot
Prices'!$C:$C),0,1,1)

and so on...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy