looking up values, Macro's

[Cool Kyle]

Hi,
I have am in purchasing and am currently working on a way to automate some of my looking up duties. I do not know how to attach the spread sheet I am having trouble with on this forum so I will try to explain as best I can.
I have created a spreadsheet that has product numbers on the y-axis, and dates on the x-axis. This spreadsheet is populated by purchase orders (i.e. product number 2 is due on Jan 3, 2006). I want to referance the dates to another spreadsheet that has product numbers, how much is scheduled and how much we have in stock. I want to add a column that will have the dates the P.O.'s are due.
I can not wrap my head around how to referance the date cells. The date cells would be the cells at the top of (x-axis) my spreadsheet whenever a cell has a non 0 number in any of the fields. Any help would be appreciated.
P.S.: I would like to learn about Macro's, I have taken a course at the local college but they tell me I will not lear about it until I have taken three other courses and even then I will not be able to edit any macro's that I make. Can someone point me to a good book or other resourse on the subject of Macro's? I see some books on this site do any of them show you how to build and work with Macro's?
Thanks.
Cool Kyle.

 

[securityman]

The book VBA and Macros for Microsoft Excel is a very good learning book. This book has helped me with macros alot.

 

[Joe Was]

1st off spreadsheets refer to data in terms of Row' and Columns, unless you are talking about a Chart.

You can do most anything in Excel, so you need to be explicit about what you have and where it is and what you want to end up with, if you want more than general help!

I take it that the product #'s are in one column each number to a row?

And you have dates in the other columns each set of dates keyed to a Product # and in the same row, but different columns.

This can be most anything [Help make us understand you!]: "I want to referance the dates to another spreadsheet that has product numbers, how much is scheduled and how much we have in stock. I want to add a column that will have the dates the P.O.'s are due."

 

[Cool Kyle]

Hi Joe,
Thank you for the reply. I am an economist so I am used to explaining things by axis. I will endeavour to be more specific, without being too long winded.
My file ("Book 8") as of now has 5 sheets called (Master, Outer stock, Scheduled, Purchase orders, Carton counts).
"Purchase orders" is arranged with date labels in cells b2 through z2 ("jan 2, 06 = b2, jan 3, 06=b3...), and Item numbers in a3 through a5 ("a3= "1", a4= "2"...). So if I have a purchase order due for product # 2 on Jan 5, "purchase orders" will have a quantity in cell "e4". I am trying to come up with a formula that has if any cell in the same row as product this example product 2 has a value than return the corresponding date. The date will be the cell in the 2nd from the top row in the corresonding column as the one that has a value in it, in this case "e2".
I don't know how to make the referance flexible enough to give me only the corresponding dates that I want for any item.
If you can get me this far I am sure I can get the rest of the way.
Thank You.

 

[Joe Was]

If the date is always two cells above the amount of product cell then you can use the "OffSet" function or the "Indirect" function.

I tend to use VBA code [macros] to do things like this, but we have some MVP's who use Sheet Formulas most often.

Normally we build spreadsheets as records in a table:

Item: then all the other data for that item in the same row, but different cells. Dates are not divided into month, day, year cells, each m/d/y date is whole and in one cell.

If your data is in this format then you can do anything with it!

You may want to build a UserForm to edit and display records of data if you want to see your data in a different arangement. Excel has a built in Data Form, I will lookup a link to how to use this and post back!


http://www.mrexcel.com/board2/viewtopic.php?t=114151

 

[Aladin Akyurek]

Hi Joe,
Thank you for the reply. I am an economist so I am used to explaining things by axis. I will endeavour to be more specific, without being too long winded.
My file ("Book 8") as of now has 5 sheets called (Master, Outer stock, Scheduled, Purchase orders, Carton counts).
"Purchase orders" is arranged with date labels in cells b2 through z2 ("jan 2, 06 = b2, jan 3, 06=b3...), and Item numbers in a3 through a5 ("a3= "1", a4= "2"...). So if I have a purchase order due for product # 2 on Jan 5, "purchase orders" will have a quantity in cell "e4". I am trying to come up with a formula that has if any cell in the same row as product this example product 2 has a value than return the corresponding date. The date will be the cell in the 2nd from the top row in the corresonding column as the one that has a value in it, in this case "e2".
I don't know how to make the referance flexible enough to give me only the corresponding dates that I want for any item.
If you can get me this far I am sure I can get the rest of the way.
Thank You.

Does the data on Purchase orders look like this: thus, just one value per row for the corresponding item?

Book6
	A	B	C	D	E	F	G
1
2		2-Jan-06	3-Jan-06	4-Jan-06	5-Jan-06	6-Jan-06	7-Jan-06
3	1				12
4	2	19
5	4					11
6	5					12
7	6		14
8
Purchase orders

 

[Cool Kyle]

Hi Aladin,
Yes, that is what the file looks like.

Thank you and have a Merry Christmas.

Cool Kyle.

 

[Aladin Akyurek]

Hi Aladin,
Yes, that is what the file looks like.

Thank you and have a Merry Christmas.

Cool Kyle.

Book6
	A	B	C	D	E	F	G
2		2-Jan-06	3-Jan-06	4-Jan-06	5-Jan-06	6-Jan-06	7-Jan-06
3	1				12
4	2	19
5	4					11
6	5					12
7	6		14
8
9
10
11	1	5-Jan-06
12	3	#N/A
13	5	6-Jan-06
14	4	6-Jan-06
15	6	3-Jan-06
16
Purchase orders

B11, copied down:

=LOOKUP(9.99999999999999E+307,INDEX($B$3:$G$7,MATCH(A11,$A$3:$A$7,0),0),$B$2:$G$2)

 

[Cool Kyle]

thanks.

Hi Aladin,
That formula worked great, thanks.