lookup question to automate spreadsheet

Dear All,

I have here one excel file called ( Daily_PRICING .xls ), located on my C Harddrive.

This File ( workbook ), contain 4 Worksheets , as following:-

Sheet-1 - For Our First Product, which is PROD_1
Sheet-2 - For Our Second Product, which is PROD_2
Sheet-3 - For Our Third Product, which is PROD_3
Sheet-4 - For Daily Report



The First sheet which is called ( PROD_1 ) , have 6 colums, which are :-

1- Date
2-Low Price
3-High Price
4-Average Price
5-Average Price Market
6-Change.

On every day morning, i used to enter the low price, high price and then i got automatic the others ( Average Price-Average Price Market-Change ).


The One which i need your Help is in the Sheet No 4.

Sheet No 4, we need to send it everyday, and it should have the following :-

1- Product Name
2-Date
3-Current Price.

I want only to enter the Product Name , and then automatically, it needs to read the Date Automatically, from the date which i enter it before, and then on the current price, it will take the Avergae for that day.

I want to make it automatic.

Can you please tell me how to do it, either manually, or using macro.

waiting for your help.

This is really what i want to know.

But i want you to explain to me the structure of the formulla .

Here is the formulla :-

==========================================================
=IF(ISNA(LOOKUP(9.99999999999999E+307,INDIRECT(A2&"!E:E"))),"Not found!!",LOOKUP(9.99999999999999E+307,INDIRECT(A2&"!E:E")))

==========================================================

Let us have a look to each fucntion :-

The function which is called - ISNA- is for the following :-

Its one of the IS Function, and its for (Value refers to the #N/A (value not available) error value ) .

Or

Returns TRUE if the value is the #N/A error value.

Ok,

Please explain to me the structure , becasue i want to implement it in my real enviroment.

waiting for your reply.

Rola,

[email protected]

you said

The indirect function is used to determine which sheet to look up.

e.g If A2 says PROD_2 it looks at that sheet in Col A and then the Lookup finds the last cell in the col.

and the formula as following :-

=IF(ISNA(LOOKUP(9.99999999999999E+307,INDIRECT(A2&"!E:E"))),"Not found!!",LOOKUP(9.99999999999999E+307,INDIRECT(A2&"!E:E")))

How can i determine for him / or how to instruct him to search for specific sheet ? for example, if the sheet name is ( NAPHTA ), how can i write it ?

and how can i determine for him to look for a specifc column ?

PLEASE EXPLAIN TO ME .