Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
I have asked this question before but the response I got didnt work. I want
to copy the following formula with only one part of the formula incrementing: ="PO# 6600"!F$7 When I try to copy this formula vertically it does not increment the 6600. It keeps it the same and that is the problem. I want the 6600 to increment by 1 each time I copy it. Any help on this will be greatly appreciated. This formula is referencing another sheet within the workbook and copying the contents of F7 into this cell. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
"cac1966" wrote:
I have asked this question before but the response I got didnt work ... Aha, you're not fair to me. Did you see my prompt, comprehensive 2nd response to you on 13 Mar, http://tinyurl.com/3czmlo (I requested for your further feedback then, but was rewarded with none) Here's the further response to you on 13 Mar: .. The original formula I used was ='PO#6600'!f7 and it worked fine. .. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. Yes, think I got your intent correctly earlier. The suggested formulas should have worked*. They work ok here when I tested them prior to posting the response. *except for a minor adjustment in the number "6600" (I had a mistake there, it should have been "6599") Assuming the formulas are to be copied down Try it again, adjusted like this, in B2: =INDIRECT("'PO#"&ROW(A1)+6599&"'!F7") Copy B2 down (To avoid typos in reproducing the formula over there, try a direct copy of the above formula from this post, then paste it into the formula bar for B2) In B2, the above would return the same as your link formula: ='PO#6600'!F7 And when B2 is copied down to B3, the formula will increment** to return the same as the link formula: ='PO#6601'!F7, and so on, giving you exactly what you want. **the incrementing is via this part in the formula: ROW(A1)+6599 Note: If you're copying across instead of down, use in B2: =INDIRECT("'PO#"&COLUMN(A1)+6599&"'!F7") If you get #REF errors, this probably means that there's some inconsistency between the actual sheetnames and the sheetnames stringed together within the INDIRECT. Both must match exactly (except for case). Recheck the actual sheetnames that you have. Watch out for any trailing whitespaces in the actual sheetnames. These are hard to see and will throw the matching off. Of course, any new sheetnames that don't exist as yet will return #REF Try it again, let me know here how it went for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: Thanks max, it didnt work a #ref1 was returned. I tried to manipulate the formula but I kept getting the same error. Here is an example of what I am trying to do: PO# Date Description... 6600 * 6601 * ... in the date column I want it to fill in the dates from the Purchase orders automatically. I referenced the PO#'s originally in the formula and it would extract the dates, descriptions, etc.. The original formular I used was ='PO#6600'!f7 and it worked fine. In order for it to do this with all the Purchase Orders I had to change the "6600" in the formula to 6601, 6602, etc. and the "f7" referenced the date from each PO. When I copied the formula it would change the F7 and not the PO#. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. If you could provide more help I would greatly appreciate it. Rick -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: I have asked this question before but the response I got didnt work. I want to copy the following formula with only one part of the formula incrementing: ="PO# 6600"!F$7 When I try to copy this formula vertically it does not increment the 6600. It keeps it the same and that is the problem. I want the 6600 to increment by 1 each time I copy it. Any help on this will be greatly appreciated. This formula is referencing another sheet within the workbook and copying the contents of F7 into this cell. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
Max,
I still didnt work....if i type ="PO# 6600"!F7 it returns the text I want off of that sheet. If I put your formula in B2 it returns a #REF error. I typed it in and also copied from the posting both ways it returns the same thing. I know the PO# tab is typed correctly because my formula returns exactly what I want. Here is an example of what I am doing A B 1 6600 2 6601 I put my formula in B1 and it returns the text from C7 on that particular PO tab. If I put your formula in either B1 or B2 it gives me the error. I have checked the formula and it is exactly what you sent me and like I said I copied it from the posting also. The information I want to appear in the B column is the name of the company which I issue a Purchase Order too. It comes from the tab marked PO# 6600 in the same workbook. This particular sheet is a different tab within the workbook and I want it to automatically fill in the vendor when I type it on the PO sheet. Sorry I didnt respond to you earlier I have gotten disgusted with this. Since I have to duplicate this formula several hundred times I dont have the time to go back and change each one of them manually. Rick "Max" wrote: "cac1966" wrote: I have asked this question before but the response I got didnt work ... Aha, you're not fair to me. Did you see my prompt, comprehensive 2nd response to you on 13 Mar, http://tinyurl.com/3czmlo (I requested for your further feedback then, but was rewarded with none) Here's the further response to you on 13 Mar: .. The original formula I used was ='PO#6600'!f7 and it worked fine. .. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. Yes, think I got your intent correctly earlier. The suggested formulas should have worked*. They work ok here when I tested them prior to posting the response. *except for a minor adjustment in the number "6600" (I had a mistake there, it should have been "6599") Assuming the formulas are to be copied down Try it again, adjusted like this, in B2: =INDIRECT("'PO#"&ROW(A1)+6599&"'!F7") Copy B2 down (To avoid typos in reproducing the formula over there, try a direct copy of the above formula from this post, then paste it into the formula bar for B2) In B2, the above would return the same as your link formula: ='PO#6600'!F7 And when B2 is copied down to B3, the formula will increment** to return the same as the link formula: ='PO#6601'!F7, and so on, giving you exactly what you want. **the incrementing is via this part in the formula: ROW(A1)+6599 Note: If you're copying across instead of down, use in B2: =INDIRECT("'PO#"&COLUMN(A1)+6599&"'!F7") If you get #REF errors, this probably means that there's some inconsistency between the actual sheetnames and the sheetnames stringed together within the INDIRECT. Both must match exactly (except for case). Recheck the actual sheetnames that you have. Watch out for any trailing whitespaces in the actual sheetnames. These are hard to see and will throw the matching off. Of course, any new sheetnames that don't exist as yet will return #REF Try it again, let me know here how it went for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: Thanks max, it didnt work a #ref1 was returned. I tried to manipulate the formula but I kept getting the same error. Here is an example of what I am trying to do: PO# Date Description... 6600 * 6601 * ... in the date column I want it to fill in the dates from the Purchase orders automatically. I referenced the PO#'s originally in the formula and it would extract the dates, descriptions, etc.. The original formular I used was ='PO#6600'!f7 and it worked fine. In order for it to do this with all the Purchase Orders I had to change the "6600" in the formula to 6601, 6602, etc. and the "f7" referenced the date from each PO. When I copied the formula it would change the F7 and not the PO#. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. If you could provide more help I would greatly appreciate it. Rick -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: I have asked this question before but the response I got didnt work. I want to copy the following formula with only one part of the formula incrementing: ="PO# 6600"!F$7 When I try to copy this formula vertically it does not increment the 6600. It keeps it the same and that is the problem. I want the 6600 to increment by 1 each time I copy it. Any help on this will be greatly appreciated. This formula is referencing another sheet within the workbook and copying the contents of F7 into this cell. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
The difference between the formula you are now telling us works, and the one
that you asked for in the post on which Max based his formula, is that you have now got a space between the # symbol and the number 6000. [The other difference is that in today's message you've got double quotes, but I assume that you intended single quotes as you had before? Please may I recommend that you don't try to retype a formula. Copy it from the formula bar and paste it in the message, then you won't get transcription errors.] If you've got that space in the shhet name, try changing Max's formula from =INDIRECT("'PO#"&ROW(A1)+6599&"'!F7") to =INDIRECT("'PO# "&ROW(A1)+6599&"'!F7") -- David Biddulph "cac1966" wrote in message ... Max, I still didnt work....if i type ="PO# 6600"!F7 it returns the text I want off of that sheet. If I put your formula in B2 it returns a #REF error. I typed it in and also copied from the posting both ways it returns the same thing. I know the PO# tab is typed correctly because my formula returns exactly what I want. Here is an example of what I am doing A B 1 6600 2 6601 I put my formula in B1 and it returns the text from C7 on that particular PO tab. If I put your formula in either B1 or B2 it gives me the error. I have checked the formula and it is exactly what you sent me and like I said I copied it from the posting also. The information I want to appear in the B column is the name of the company which I issue a Purchase Order too. It comes from the tab marked PO# 6600 in the same workbook. This particular sheet is a different tab within the workbook and I want it to automatically fill in the vendor when I type it on the PO sheet. Sorry I didnt respond to you earlier I have gotten disgusted with this. Since I have to duplicate this formula several hundred times I dont have the time to go back and change each one of them manually. Rick "Max" wrote: "cac1966" wrote: I have asked this question before but the response I got didnt work ... Aha, you're not fair to me. Did you see my prompt, comprehensive 2nd response to you on 13 Mar, http://tinyurl.com/3czmlo (I requested for your further feedback then, but was rewarded with none) Here's the further response to you on 13 Mar: .. The original formula I used was ='PO#6600'!f7 and it worked fine. .. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. Yes, think I got your intent correctly earlier. The suggested formulas should have worked*. They work ok here when I tested them prior to posting the response. *except for a minor adjustment in the number "6600" (I had a mistake there, it should have been "6599") Assuming the formulas are to be copied down Try it again, adjusted like this, in B2: =INDIRECT("'PO#"&ROW(A1)+6599&"'!F7") Copy B2 down (To avoid typos in reproducing the formula over there, try a direct copy of the above formula from this post, then paste it into the formula bar for B2) In B2, the above would return the same as your link formula: ='PO#6600'!F7 And when B2 is copied down to B3, the formula will increment** to return the same as the link formula: ='PO#6601'!F7, and so on, giving you exactly what you want. **the incrementing is via this part in the formula: ROW(A1)+6599 Note: If you're copying across instead of down, use in B2: =INDIRECT("'PO#"&COLUMN(A1)+6599&"'!F7") If you get #REF errors, this probably means that there's some inconsistency between the actual sheetnames and the sheetnames stringed together within the INDIRECT. Both must match exactly (except for case). Recheck the actual sheetnames that you have. Watch out for any trailing whitespaces in the actual sheetnames. These are hard to see and will throw the matching off. Of course, any new sheetnames that don't exist as yet will return #REF Try it again, let me know here how it went for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: Thanks max, it didnt work a #ref1 was returned. I tried to manipulate the formula but I kept getting the same error. Here is an example of what I am trying to do: PO# Date Description... 6600 * 6601 * ... in the date column I want it to fill in the dates from the Purchase orders automatically. I referenced the PO#'s originally in the formula and it would extract the dates, descriptions, etc.. The original formular I used was ='PO#6600'!f7 and it worked fine. In order for it to do this with all the Purchase Orders I had to change the "6600" in the formula to 6601, 6602, etc. and the "f7" referenced the date from each PO. When I copied the formula it would change the F7 and not the PO#. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. If you could provide more help I would greatly appreciate it. Rick -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: I have asked this question before but the response I got didnt work. I want to copy the following formula with only one part of the formula incrementing: ="PO# 6600"!F$7 When I try to copy this formula vertically it does not increment the 6600. It keeps it the same and that is the problem. I want the 6600 to increment by 1 each time I copy it. Any help on this will be greatly appreciated. This formula is referencing another sheet within the workbook and copying the contents of F7 into this cell. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
Awesome David!!! That was the problem the space in the formula now it works
perfect. Thanks to you and Max for all your help!!!!!! "cac1966" wrote: I have asked this question before but the response I got didnt work. I want to copy the following formula with only one part of the formula incrementing: ="PO# 6600"!F$7 When I try to copy this formula vertically it does not increment the 6600. It keeps it the same and that is the problem. I want the 6600 to increment by 1 each time I copy it. Any help on this will be greatly appreciated. This formula is referencing another sheet within the workbook and copying the contents of F7 into this cell. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
One more question....when I copy this formula and there is not a purchase
order made for it yet, it returns a #ref! error. I want to hide this on the ledger. I tried conditional formatting and white out the word but it didnt work. I have a column of #ref! now is there anything I can do to hide these until the Purchase order has been entered? "David Biddulph" wrote: The difference between the formula you are now telling us works, and the one that you asked for in the post on which Max based his formula, is that you have now got a space between the # symbol and the number 6000. [The other difference is that in today's message you've got double quotes, but I assume that you intended single quotes as you had before? Please may I recommend that you don't try to retype a formula. Copy it from the formula bar and paste it in the message, then you won't get transcription errors.] If you've got that space in the shhet name, try changing Max's formula from =INDIRECT("'PO#"&ROW(A1)+6599&"'!F7") to =INDIRECT("'PO# "&ROW(A1)+6599&"'!F7") -- David Biddulph "cac1966" wrote in message ... Max, I still didnt work....if i type ="PO# 6600"!F7 it returns the text I want off of that sheet. If I put your formula in B2 it returns a #REF error. I typed it in and also copied from the posting both ways it returns the same thing. I know the PO# tab is typed correctly because my formula returns exactly what I want. Here is an example of what I am doing A B 1 6600 2 6601 I put my formula in B1 and it returns the text from C7 on that particular PO tab. If I put your formula in either B1 or B2 it gives me the error. I have checked the formula and it is exactly what you sent me and like I said I copied it from the posting also. The information I want to appear in the B column is the name of the company which I issue a Purchase Order too. It comes from the tab marked PO# 6600 in the same workbook. This particular sheet is a different tab within the workbook and I want it to automatically fill in the vendor when I type it on the PO sheet. Sorry I didnt respond to you earlier I have gotten disgusted with this. Since I have to duplicate this formula several hundred times I dont have the time to go back and change each one of them manually. Rick "Max" wrote: "cac1966" wrote: I have asked this question before but the response I got didnt work ... Aha, you're not fair to me. Did you see my prompt, comprehensive 2nd response to you on 13 Mar, http://tinyurl.com/3czmlo (I requested for your further feedback then, but was rewarded with none) Here's the further response to you on 13 Mar: .. The original formula I used was ='PO#6600'!f7 and it worked fine. .. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. Yes, think I got your intent correctly earlier. The suggested formulas should have worked*. They work ok here when I tested them prior to posting the response. *except for a minor adjustment in the number "6600" (I had a mistake there, it should have been "6599") Assuming the formulas are to be copied down Try it again, adjusted like this, in B2: =INDIRECT("'PO#"&ROW(A1)+6599&"'!F7") Copy B2 down (To avoid typos in reproducing the formula over there, try a direct copy of the above formula from this post, then paste it into the formula bar for B2) In B2, the above would return the same as your link formula: ='PO#6600'!F7 And when B2 is copied down to B3, the formula will increment** to return the same as the link formula: ='PO#6601'!F7, and so on, giving you exactly what you want. **the incrementing is via this part in the formula: ROW(A1)+6599 Note: If you're copying across instead of down, use in B2: =INDIRECT("'PO#"&COLUMN(A1)+6599&"'!F7") If you get #REF errors, this probably means that there's some inconsistency between the actual sheetnames and the sheetnames stringed together within the INDIRECT. Both must match exactly (except for case). Recheck the actual sheetnames that you have. Watch out for any trailing whitespaces in the actual sheetnames. These are hard to see and will throw the matching off. Of course, any new sheetnames that don't exist as yet will return #REF Try it again, let me know here how it went for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: Thanks max, it didnt work a #ref1 was returned. I tried to manipulate the formula but I kept getting the same error. Here is an example of what I am trying to do: PO# Date Description... 6600 * 6601 * ... in the date column I want it to fill in the dates from the Purchase orders automatically. I referenced the PO#'s originally in the formula and it would extract the dates, descriptions, etc.. The original formular I used was ='PO#6600'!f7 and it worked fine. In order for it to do this with all the Purchase Orders I had to change the "6600" in the formula to 6601, 6602, etc. and the "f7" referenced the date from each PO. When I copied the formula it would change the F7 and not the PO#. Basically I want the opposite. The F7 to stay the same and the PO# to change by increments of 1. If you could provide more help I would greatly appreciate it. Rick -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cac1966" wrote: I have asked this question before but the response I got didnt work. I want to copy the following formula with only one part of the formula incrementing: ="PO# 6600"!F$7 When I try to copy this formula vertically it does not increment the 6600. It keeps it the same and that is the problem. I want the 6600 to increment by 1 each time I copy it. Any help on this will be greatly appreciated. This formula is referencing another sheet within the workbook and copying the contents of F7 into this cell. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
"cac1966" wrote:
Awesome David!!! That was the problem the space in the formula now it works perfect. Thanks to you and Max for all your help!!!!!! We could have saved all this trouble had you been more careful in your original posting on 13 Mar, http://tinyurl.com/3czmlo) where you had indicated your example as: ='PO#6601'!F7. My original response to you then was spot on based on what you posted, yet you rated it as not helpful. You should redress the wrong rating given. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
That was the original formula I used and it worked. When I put the space in
the formula and hit enter it removed the space. Thanks for all your help! "Max" wrote: "cac1966" wrote: Awesome David!!! That was the problem the space in the formula now it works perfect. Thanks to you and Max for all your help!!!!!! We could have saved all this trouble had you been more careful in your original posting on 13 Mar, http://tinyurl.com/3czmlo) where you had indicated your example as: ='PO#6601'!F7. My original response to you then was spot on based on what you posted, yet you rated it as not helpful. You should redress the wrong rating given. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying formula
"cac1966" wrote:
One more question....when I copy this formula and there is not a purchase order made for it yet, it returns a #ref! error. Yup, that's as stated in my earlier response to you on 13 Mar Of course, any new sheetnames that don't exist as yet will return #REF I want to hide this on the ledger. I tried conditional formatting and white out the word but it didnt work. I have a column of #ref! now is there anything I can do to hide these until the Purchase order has been entered? Use ISERROR to mask in CF .. Try it like this, to apply it all at one go: Select the entire col B (B1 active) Click Format Conditional Formatting Under Condition 1, make the settings as: Formula is: =ISERROR(B1) Format the font as white, Ok out -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Formula | Excel Discussion (Misc queries) | |||
copying formula | Excel Worksheet Functions | |||
Formula not copying down | Excel Worksheet Functions | |||
copying formula | Excel Discussion (Misc queries) | |||
copying a formula | Excel Discussion (Misc queries) |