So I've searched high and low and cannot seem to find an answer to this question. Spent almost two days trying every thing I could find on line, but none were specific enough to my situation. Without going in by hand and amending each cell, how can I populate down the list in the order they appear, the names of each sheet to pull a date?

I'm using excel 2013 BTW.

The Live Inventory sheet is a log that holds the current numbers for our Quantity On Hand. I update these via daily reports in each of the tabs. In those tabs we track each flavor by LOT# (Expiration Date) these dates are entered manually on each of the tabs as we add new LOT# to the report. However, I want these to be reflected and pulled from the tabs onto the main sheet.

For example, the first LOT# for the first flavor, Pineapple, is in cell D3 on the Live Inventory Sheet. The cell this should be pulled from is on the Sheet named Pineapple and is located in cell D$2.

Across the LOT#'s it should be

"!Pineapple" D2 = "!Live Inventory Sheet" D3

"!Pineapple" I2 = "!Live Inventory Sheet" F3

"!Pineapple" N2 = "!Live Inventory Sheet" H3

"!Pineapple" S2 = "!Live Inventory Sheet" J3

"!Pineapple" X2 = "!Live Inventory Sheet" L3

"!Pineapple" AC2 = "!Live Inventory Sheet" N3

"!Pineapple" AH2 = "!Live Inventory Sheet" P3

"!Pineapple" AM2 = "!Live Inventory Sheet" R3

"!Pineapple" AR2 = "!Live Inventory Sheet" T3

"!Pineapple" AW2 = "!Live Inventory Sheet" V3

"!Pineapple" BB2 = "!Live Inventory Sheet" X3

I have tried to attach pics. Sorry if this was too vague, I will try and answer more if needed.

Also, if it is easier, the names of each of the sheets is listed down Column B, if you can somehow reference that into an equation.

enter image description here

enter image description here


I think you want:

='Live Inventory Sheet'!D3

Put this in the Pineapple sheet on cell D2.

Do the same for each item in your list.

  • You can get Excel to generate this for you by clicking on the cell D2 in Pineapple, hitting the = key to begin a formula, and then clicking on the cell D3 in Live Inventory Sheet. – Engineer Toast Mar 23 '15 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.