Trying to make a scenario manager in excel for cars and engine and body costs.
1. select car model in A3.
2. have a table of results based on base case cost for all models. (linked by data validation to sheet 2).
3. be able to change the engine and body cost in cells C3 and C5 in sheet 1, and see what effect that would have on the cost of all the car models assuming the same % increase/ decrease was applied.
4. plot results on graph on same worksheet.
Example attached.
1st remove the trailing space in sheet 2 Body
Then, in B4, you can use this...
=INDEX(Sheet2!$C$2:$F$4,MATCH(Sheet1!$B$3,Sheet2!$B$2:$B$4,0),MATCH(Sheet1!$A4,Sheet2!$C$1:$F$1,0))
copied down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
thanks
what im trying to do is get the table in sheet 1 populated for each scanario base and adjusted as shown on right of sheet 1
heres updated file with index function updated
Last edited by alive555; 08-24-2015 at 03:09 AM.
does anyone know how to fix this ?
You missed this part...
1st remove the trailing space in sheet 2 Body
sorry there is no trailing space in sheet 2 Body ??
also the purpose is to p[opulate the table in sheet 1 in cells G4:L5
Look at sheet 2 cell C1. You have text in that cell that says Body. There is an extra space at the end of the word, remove it, and the formula will work
aha! well spotted
however the formula doesnt populate the data in the table which is the objective
anyone ?
aha! well spotted
however the formula doesnt populate the data in the table which is the objective
anyone ?
Since the analysis is simple, ou can just use the INDEX MATCH function you have in B4 and B5, and use it in the table that you have and then sensitize them accordnigly.
Row\Col Ford Ford Toyota Toyota BMW BMW Ford Toyota BMW Base Upper Base Upper Base Upper 100 110.00 80 88.00 200 220.00 100 103.00 80 82.40 200 206.00 200 213 160 170.4 400 426
Add on row 1 the car brand names then...
Formula:
G4: =INDEX(Sheet2!$B$2:$E$4,MATCH(G$1,Sheet2!$A$2:$A$4,0),MATCH($A4,Sheet2!$B$1:$E$1,0))
Copied to G5, I4:I5, K4:K5
H4: =G4*(1+$C4)
Copied to H5, J4:J5, L4:L5
Deleted - doublepost
thanks . cant recreate in this file. can fix pls ?
Yes i made a new model and tried to incorporate the formula but got stuck. can u fix this pls ?
Where is your DD, where do you want teh answers?? That file looks nothing like your 1st sample WB
i want the answers in the yellow cells by changing A1
THANKS
does anyone know how to change one cell and fill in a table of calculated values ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks