Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup or Macro?
Hi,
I have a massive excel book with the following, 1) Sheet 1 is the Efforts sheet (10000 rows) & there is - one column per month (so there are like 30 months column) - one column for Team Name & - one column for a Unique ID 2) Sheet 2 is the Cost Sheet (10000 rows) - Mirror image of Efforts sheet including columns 3) Sheet 3 is the Cost/Team Look up (10 to 12 rows) - One column for team name & One column for Price for Team per hr What is desired? 1) As we enter the efforts, the cost is updated based one the following - Cost = Effort * Price for Team per hr 2) It should be possible to filter data to see in efforts. My Current Solution? INDEX(Effort!Range;MATCH(Unique ID;Range;0);2) *VLOOKUP(TRIM(Team_Name);Sheet3!Range;nPrice_Colum n;FALSE) or another VLOOKUP instead of Index. Problems: 1) Performance problems: I understand that the formula above is being run in all the cells (30 Cols * 10000 Rows) and it takes like 4 to 5 mins. TO Note: 1) Not all efforts in efforts sheet are changed all the time Questions? 1) Is it possible to only use changed cells to be recalculated? (Today when i just apply filter it will cause the entire sheet to recalculate) 2) Is it better to use Macro to identify and isolate the changed cells? Thanks a lot Karthik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in a Macro | Excel Programming | |||
Vlookup Macro? | Excel Discussion (Misc queries) | |||
vlookup macro | New Users to Excel | |||
VLOOKUP in a macro?? | Excel Programming | |||
VLOOKUP in a macro?? | Excel Discussion (Misc queries) |