Excel Help

[flamesrule_kipper34]

Hello all,

So I need some help trying to figure out options or methods of doing something I'm trying to automate.

Below is a link to a test file that you're free to look at while understanding my question(s).

In the first sheet I have a drop-down menu in which a user would select any month-year. And then below this are two tables in which they'd fill out manually with their respective values (right now I've just put in a RAND formula to make up numbers to help the example), all orange cells are calculation cells that they do not need to fill out.

Below the table, under each column of values are 'codes' that drive off the name of the values in the table and the month selected in the drop-down menu.

The other 3 sheets have these same codes, a particular row of Column D...Column-CN has the month and YTD codes. Whereas Column A has the name value codes.

Basically I somehow want the users to be able to enter in the values in the initial table and then somehow through matching the codes from that original sheet to codes in the other sheet those values are copied and pasted automatically or through some process of automation.

I'm not sure how to tackle this, any thought, ideas, help, suggestions would be great.

Thanks!


https://docs.google.com/leaf?id=0B1m...hMWQ4&hl=en_US

[Northendzone]

Perhaps this might not be the best way, but I might concatenate some stuff together and then use some V or H Lookups and then perhaps an if statement.

[flamesrule_kipper34]

But would that require concatenating and V H lookups on every cell for every month and metric in every sheet where the transfer of values from the table to other sheets would occur?

[flamesrule_kipper34]

Okay trying to figure out what I'm sure is quite simple but if I have the following code:

Sub Macro2()

Cells.Find(What:="Lubricant A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy Destination:=SheetCells.Find(What:="Lubricant A", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Offset(0, 1)
End Sub

Now I want it to search for "Lubricant A" in the active sheet BUT look for and paste the value to "Lubricant A" into another sheet how can I do that? Because right now with the above code it will look for Lubricant A on the same sheet and obviously fail. Help? Thanks.

[calumniate]

Hmm, out of curiousity do you have excel 2010? If so I may suggest downloading the powerpivot. Would be a bit of a paradigm shift for you perhaps, but would recommend you get your data into a 1 or 3 flat-files, and you could pick months / manipulate data and run calculations as you wish.

[flamesrule_kipper34]

Unfortunately I have Excel 2007 and the work I'm doing is for WORK and everyone running what I'm trying to create/operate would have to be through Excel 2007 compatibility so I doubt I could do it the way you suggest.

[flamesrule_kipper34]

Solution to the one part is: http://www.ozgrid.com/forum/showthre...982#post578982