| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I have to change a bunch of formula that need to reference a different workbook.
The one workbook will have a price list the other will have all the quote
sheets. At first I had a page with the mark ups and hourly rate in the first workbook that they were used on another page to mark up the products. But I realized that this wouldn't work because an changes in the first book would make those changes to the other work books when they got opened - creating a huge can of worms so no I need to move the control sheet to the quote work book. But this will mean changes all the formula a very time consuming and tedious process. Can I set up the formula in the control worksheet and have it as a cell reference effect the contents of that cell. This is one of the formulas that I need to use: =IF(Questionaire!$F$16=1,I1073,IF(Questionaire!$F$ 16=2,I1073*'Look up tables'!$D$9,IF(Questionaire!$F$16=3,I1073*'Look up tables'!$D$10,IF(Questionaire!$F$16=4,I1073*'Look up tables'!$D$11,IF(Questionaire!$F$16=5,I1073*'Look up tables'!$D$12,IF(Questionaire!$F$16=6,I1073*'Look up tables'!$D$13,IF(Questionaire!$F$16=7,I1073*'Look up tables'!$D$14,IF(Questionaire!$F$16=8,I1073*'Look up tables'!$D$15,"")))))) )) Any suggestions? Marc |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I have to change a bunch of formula that need to reference a different workbook.
It sounds like you may be doing an easy job the hard way. I suggest you post a sample of data and a description of what you want to acheive, then see if someone can help with an easier method. Matt -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=561793 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I have to change a bunch of formula that need to reference a diffe
Where you put it does not matter, as it will always affect all other entries,
when you make changes. The only way to overcome this kind of problem is to still use formulae to calculate, but then to copy your results and paste special, eg into a quote template. Iow, you will have a calculation sheet and a quote sheet. You do your calculations in the calculation sheet, and then, using a macro, copy the results to the quote sheet, and paste special as values. Your IF story is very complicated and I think unnecessary. You could create a lookup table that says what needs to happen for each condition, and then use VLOOKUP to do the calculations for you. May be way off beat here, but without any real info, that's what it looks like to me "Marc" wrote: The one workbook will have a price list the other will have all the quote sheets. At first I had a page with the mark ups and hourly rate in the first workbook that they were used on another page to mark up the products. But I realized that this wouldn't work because an changes in the first book would make those changes to the other work books when they got opened - creating a huge can of worms so no I need to move the control sheet to the quote work book. But this will mean changes all the formula a very time consuming and tedious process. Can I set up the formula in the control worksheet and have it as a cell reference effect the contents of that cell. This is one of the formulas that I need to use: =IF(Questionaire!$F$16=1,I1073,IF(Questionaire!$F$ 16=2,I1073*'Look up tables'!$D$9,IF(Questionaire!$F$16=3,I1073*'Look up tables'!$D$10,IF(Questionaire!$F$16=4,I1073*'Look up tables'!$D$11,IF(Questionaire!$F$16=5,I1073*'Look up tables'!$D$12,IF(Questionaire!$F$16=6,I1073*'Look up tables'!$D$13,IF(Questionaire!$F$16=7,I1073*'Look up tables'!$D$14,IF(Questionaire!$F$16=8,I1073*'Look up tables'!$D$15,"")))))) )) Any suggestions? Marc |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I have to change a bunch of formula that need to reference a diffe
The IF statement is based on the square footage of the house. The large the
house the more expensive to pre-wire the refences to the "Questionaire" is where the drop down with the square footage option are located. The actual multipliers are on the "Look up tables" sheet. I'd be happy to make this simplier but without understanding Macros this is the best way that I know. I'd be apprecaitive of any help. Marc tables' "kassie" wrote in message ... Where you put it does not matter, as it will always affect all other entries, when you make changes. The only way to overcome this kind of problem is to still use formulae to calculate, but then to copy your results and paste special, eg into a quote template. Iow, you will have a calculation sheet and a quote sheet. You do your calculations in the calculation sheet, and then, using a macro, copy the results to the quote sheet, and paste special as values. Your IF story is very complicated and I think unnecessary. You could create a lookup table that says what needs to happen for each condition, and then use VLOOKUP to do the calculations for you. May be way off beat here, but without any real info, that's what it looks like to me "Marc" wrote: The one workbook will have a price list the other will have all the quote sheets. At first I had a page with the mark ups and hourly rate in the first workbook that they were used on another page to mark up the products. But I realized that this wouldn't work because an changes in the first book would make those changes to the other work books when they got opened - creating a huge can of worms so no I need to move the control sheet to the quote work book. But this will mean changes all the formula a very time consuming and tedious process. Can I set up the formula in the control worksheet and have it as a cell reference effect the contents of that cell. This is one of the formulas that I need to use: =IF(Questionaire!$F$16=1,I1073,IF(Questionaire!$F$ 16=2,I1073*'Look up tables'!$D$9,IF(Questionaire!$F$16=3,I1073*'Look up tables'!$D$10,IF(Questionaire!$F$16=4,I1073*'Look up tables'!$D$11,IF(Questionaire!$F$16=5,I1073*'Look up tables'!$D$12,IF(Questionaire!$F$16=6,I1073*'Look up tables'!$D$13,IF(Questionaire!$F$16=7,I1073*'Look up tables'!$D$14,IF(Questionaire!$F$16=8,I1073*'Look up tables'!$D$15,"")))))) )) Any suggestions? Marc |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I have to change a bunch of formula that need to reference a d
Without having detailed info about what you are trying to do here, I will not
be able to assist. If you wish, send me your file to j.kasselman@lanticdotnet_changethedot "Marc" wrote: The IF statement is based on the square footage of the house. The large the house the more expensive to pre-wire the refences to the "Questionaire" is where the drop down with the square footage option are located. The actual multipliers are on the "Look up tables" sheet. I'd be happy to make this simplier but without understanding Macros this is the best way that I know. I'd be apprecaitive of any help. Marc tables' "kassie" wrote in message ... Where you put it does not matter, as it will always affect all other entries, when you make changes. The only way to overcome this kind of problem is to still use formulae to calculate, but then to copy your results and paste special, eg into a quote template. Iow, you will have a calculation sheet and a quote sheet. You do your calculations in the calculation sheet, and then, using a macro, copy the results to the quote sheet, and paste special as values. Your IF story is very complicated and I think unnecessary. You could create a lookup table that says what needs to happen for each condition, and then use VLOOKUP to do the calculations for you. May be way off beat here, but without any real info, that's what it looks like to me "Marc" wrote: The one workbook will have a price list the other will have all the quote sheets. At first I had a page with the mark ups and hourly rate in the first workbook that they were used on another page to mark up the products. But I realized that this wouldn't work because an changes in the first book would make those changes to the other work books when they got opened - creating a huge can of worms so no I need to move the control sheet to the quote work book. But this will mean changes all the formula a very time consuming and tedious process. Can I set up the formula in the control worksheet and have it as a cell reference effect the contents of that cell. This is one of the formulas that I need to use: =IF(Questionaire!$F$16=1,I1073,IF(Questionaire!$F$ 16=2,I1073*'Look up tables'!$D$9,IF(Questionaire!$F$16=3,I1073*'Look up tables'!$D$10,IF(Questionaire!$F$16=4,I1073*'Look up tables'!$D$11,IF(Questionaire!$F$16=5,I1073*'Look up tables'!$D$12,IF(Questionaire!$F$16=6,I1073*'Look up tables'!$D$13,IF(Questionaire!$F$16=7,I1073*'Look up tables'!$D$14,IF(Questionaire!$F$16=8,I1073*'Look up tables'!$D$15,"")))))) )) Any suggestions? Marc |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I have to change a bunch of formula that need to reference a d
Thanks, Kassie
"kassie" wrote in message ... Without having detailed info about what you are trying to do here, I will not be able to assist. If you wish, send me your file to j.kasselman@lanticdotnet_changethedot "Marc" wrote: The IF statement is based on the square footage of the house. The large the house the more expensive to pre-wire the refences to the "Questionaire" is where the drop down with the square footage option are located. The actual multipliers are on the "Look up tables" sheet. I'd be happy to make this simplier but without understanding Macros this is the best way that I know. I'd be apprecaitive of any help. Marc tables' "kassie" wrote in message ... Where you put it does not matter, as it will always affect all other entries, when you make changes. The only way to overcome this kind of problem is to still use formulae to calculate, but then to copy your results and paste special, eg into a quote template. Iow, you will have a calculation sheet and a quote sheet. You do your calculations in the calculation sheet, and then, using a macro, copy the results to the quote sheet, and paste special as values. Your IF story is very complicated and I think unnecessary. You could create a lookup table that says what needs to happen for each condition, and then use VLOOKUP to do the calculations for you. May be way off beat here, but without any real info, that's what it looks like to me "Marc" wrote: The one workbook will have a price list the other will have all the quote sheets. At first I had a page with the mark ups and hourly rate in the first workbook that they were used on another page to mark up the products. But I realized that this wouldn't work because an changes in the first book would make those changes to the other work books when they got opened - creating a huge can of worms so no I need to move the control sheet to the quote work book. But this will mean changes all the formula a very time consuming and tedious process. Can I set up the formula in the control worksheet and have it as a cell reference effect the contents of that cell. This is one of the formulas that I need to use: =IF(Questionaire!$F$16=1,I1073,IF(Questionaire!$F$ 16=2,I1073*'Look up tables'!$D$9,IF(Questionaire!$F$16=3,I1073*'Look up tables'!$D$10,IF(Questionaire!$F$16=4,I1073*'Look up tables'!$D$11,IF(Questionaire!$F$16=5,I1073*'Look up tables'!$D$12,IF(Questionaire!$F$16=6,I1073*'Look up tables'!$D$13,IF(Questionaire!$F$16=7,I1073*'Look up tables'!$D$14,IF(Questionaire!$F$16=8,I1073*'Look up tables'!$D$15,"")))))) )) Any suggestions? Marc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change a tab name with cell reference and formula? | Excel Worksheet Functions | |||
| circular reference formula | Excel Discussion (Misc queries) | |||
| Change sheet reference in new workbook | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
