Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return array from worksheet function | Excel Worksheet Functions | |||
Show week number in current month | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) |