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

[Jim]

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.

What I need to know is if there is a way to automatically have NAME
functions to refrence the ROW that I have highlighted (sort of a
"return ROW" function) which would save from having to take the extra
step of manually changing the ROW numbers in the NAME definition
formulas? If it was possible to do this, I could go back any day and
get total inventory cost/value just by highliting appropriate row.

I've tried several things from the HELP file, but none have worked so
far, so as last resort am asking this newsgroup. I am pretty much a
novice in EXCEL, and am using EXCEL 2003. By using HELP file was able
to complete everything except the automatic ROW function.

Thanks for any leads,

Jim



On first sh

 

[Franz Verga]

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

 

[Jim]

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

 

[Franz Verga]

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

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

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