want to copy formula, only change one number in formula

[Guest]

I am using a main worksheet in a workbook to capture information from other
worksheets within the workbook. I want to copy formulas without changing all
the formula. I only want to change 1 value when copying. Example:

='PO#6601'!F7

The only number I want to change when copying this formula is the "6601" I
want it to increase by one each time the formula is copied. I want
everything else to stay the same especially the "F7". This program is a
purchase order program with a ledger which saves all the information with
each new purchase order I record on a separtate worksheet.

 

[Guest]

One way ..

Place this in the starting cell, eg in B2:
=INDIRECT("'PO#"&ROW(A1)+6600&"'!F7")
Copy down as far as required

If you are copying it across, use instead in B2:
=INDIRECT("'PO#"&COLUMN(A1)+6600&"'!F7")

 

[Guest]

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

 

[Guest]

.. 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.

---