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.