looking up values, Macro's

Cool Kyle

New Member
Joined
Sep 21, 2005
Messages
8
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

securityman

Board Regular
Joined
Mar 5, 2005
Messages
128
The book VBA and Macros for Microsoft Excel is a very good learning book. This book has helped me with macros alot.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Sep 21, 2005
Messages
8
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Cool Kyle said:
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
ABCDEFG
1
22-Jan-063-Jan-064-Jan-065-Jan-066-Jan-067-Jan-06
3112
4219
5411
6512
7614
8
Purchase orders
 

Cool Kyle

New Member
Joined
Sep 21, 2005
Messages
8

ADVERTISEMENT

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

Thank you and have a Merry Christmas.

Cool Kyle.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Cool Kyle said:
Hi Aladin,
Yes, that is what the file looks like.

Thank you and have a Merry Christmas.

Cool Kyle.
Book6
ABCDEFG
22-Jan-063-Jan-064-Jan-065-Jan-066-Jan-067-Jan-06
3112
4219
5411
6512
7614
8
9
10
1115-Jan-06
123#N/A
1356-Jan-06
1446-Jan-06
1563-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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,379
Members
412,589
Latest member
ArtBOM
Top