Mirroring Cell from One Sheet to Reference Sheet with Ability To Increase

I apologize in advance as I am not very knowledgable when it comes to excel formulas.

I am creating a document to log and enter purchase orders. The first sheet of my document is a log while each sheet in the log is an individual purchase order (labeled "PO (1)", "PO (2)", etc. The first sheet is meant to be a reference to different cells in its corresponding purchase order.

I was previously just using a simple formula to directly reference the cell (ie. ='PO (10)'!B9) but I have a lot of cells to mirror and a very large amount of sheets to reference and typing in each formula individually will take up way too much time. I was hoping I could select two sequential cells (ex. ='PO (10)'!B9 and ='PO (11)'!B9) and drag and increase using the black "+" cursor but this only resulted in duplicating the cells down the row.

I have been researching this for some time but cannot figure out how to get this to work. I have been toying around with the "INDIRECT" formula but have had no luck, although I think this is probably the trail I am supposed to be on.

Using the formula =INDIRECT('PO (10)'!B9) I get a #REF! error.
Using the formula =INDIRECT(ref_text,'PO (31)'!B9) I get a #NAME? error.

Can anyone give me some direction?

Thank you!

You're right that the INDIRECT function is what you need.

If you can upload a sample file I could give easier more specific help.

I am assuming you have the PO numbers already listed in your summary sheet. Let's assume it's column A starting in row 2. The formula in row 2 of the column you want to fill would be:

Formula:

Please Login or Register  to view this content.

Originally Posted by gak67

You're right that the INDIRECT function is what you need.

If you can upload a sample file I could give easier more specific help.

I am assuming you have the PO numbers already listed in your summary sheet. Let's assume it's column A starting in row 2. The formula in row 2 of the column you want to fill would be:

Formula:

Please Login or Register  to view this content.

My sheet is actually called 'PO (10)' where the next sequential sheet is called 'PO (11)' so I'm not sure if the formula you are giving me actually applies to my situation. Does that make sense?

Originally Posted by FDibbins

Hi and welcome to the forum

1st, if you have a sheet for each PO, then I have a feeling you need to rethink how you are doing this. Im not sure how many sheets excel allows, but even 50+ sheets will become a headache to navigate. Excel is pretty much designed (and works best) when all data is stored on 1 (or a few 2-3) sheets, and then other sheets are used to summarise/extract/interrogate the data.

Can you upload a (clean) sample of what you are working with, and what you want, and we can see if we can streamline this for you?

I actually have created links to each PO within the summary page and it is quite easy to navigate, along with a link back to the summary page hidden within each PO (I print the PO's for our accounting department and don't want an obvious button on the printout). I work in freelance film production so the highest amount of POs I would ever really need would be 100-150.

What is the best way to upload a sample so you can view it? You are just talking about the entire (clean) file right? Will a link on dropbox work?