K
kart.rkm
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_Column;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
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_Column;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