How do you return current ROW number to definition in NAME function?
 

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

How do you return current ROW number to definition in NAME function?
 

If you like you could try to upload an example file to www.savefile.com, so
I can try to solve it...


Jim wrote:
Franz;

Thank you, tried it but it did not work. Kept getting the Value $0.00
in the inventory section no matter what the amount or the daily price.

Jim






On Mon, 24 Jul 2006 02:35:04 +0200, "Franz Verga"
wrote:

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...

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy