copying formula

[cac1966]

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.

 

[Max]

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, re: 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.

 

[cac1966]

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

 

[David Biddulph]

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")

 

[cac1966]

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]

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?

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

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]

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, re: 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.

 

[cac1966]

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]

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

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