Cell Reference Question

[ckserra]

Hi everyone, good morning. I am adding a "Purchase Order Recap" page to a large Excel file for purchase orders. I am trying to easily pull in 4 different cells from each page. Issue is we have 300+ POs. The individual POs are setup the same. Is there a way to add the formula I need for each cell, for instance ,

='GD22-0525'!$E$16

, and drag down so it moves to GD22-0526, 0527 ETC.

If not, if the first column is PO number and I manually make that column accurate, how can I use that column as the Sheet reference in other cells, such as date and vendor? So instead of

='GD22-0525'!$E$16

it would look like =A2&!$E$16 or something like that.

Thank you so much for your time!
Chris

 

[etaf]

=INDIRECT("'"&A2&"'!$E$16")

i'm sure it possible to do the 1st option, just not sure at the moment

 

[ckserra]

=INDIRECT("'"&A2&"'!$E$16")

i'm sure it possible to do the 1st option, just not sure at the moment

Thats great, that works, thank you! What would be the best way to SUM g19:g50 on those cells using Indirect? Ive tried a few different formulas with no luck.
THank you again.

=sum(INDIRECT(a2&g19:g50)

 

[etaf]

=SUM(INDIRECT("'"&A2&"'!G19:G50"))

indirect is a volatile function , so every time you touch the spreadsheet - it has to recalculate the value - and if you have a lot of indirect functions - can slow the spreadsheet down

 

[ckserra]

=SUM(INDIRECT("'"&A2&"'!G19:G50"))

indirect is a volatile function , so every time you touch the spreadsheet - it has to recalculate the value - and if you have a lot of indirect functions - can slow the spreadsheet down

Understood and thank you gain for your help!

 

[etaf]

you are welcome