referencing another sheet using the contents and not the cell reference

[benjii19]

Hi


I have a query relating to handling a report I've imported into excel.

It contains a list of items in Column A and a list of quantities in Column B these apply to 5 sheets, with a totals sheet, the problem being that the rows can sometimes be different. Which means referencing a cell will not work as the item that appears in the cell may be different on each sheet. So can I reference to a cells contents and not the cell?


Thanks

Ben

[TMS]

So can I reference to a cells contents and not the cell?

What? Don't understand what you mean. Suggest you post a sample workbook, or two, and clarify your requirement.

If you have a reference value, you can probably use VLOOKUP or INDEX/MATCH to locate related data.


Regards, TMS

[BadlySpelledBuoy]

Could you post a sample workbook and include your expected results? That will make it far easier to provide you with a working solution.

BSB

[benjii19]

Hi, sorry about the confusion.

I've created a sample

WEEK1 and WEEK2 have the same ITEMS however WEEK3 contains some different items ITEM027 has been added and ITEM002 removed from WEEK3.


On the totals sheet I need to see all the items so that would include the addition of ITEMS027 and still include ITEM002. so the definitive list will be:-

ITEM001
ITEM002
ITEM004
ITEM005
ITEM012
ITEM023
ITEM024
ITEM026
ITEM027


When our stock system exports reports it will do all current items, which can change from week to week.


Hope this makes sense

[TMS]

Splitting data across sheets is always a bad idea ... as is evident when you try to analyse it, at least, without VBA.

Why not put all the data on one sheet and add a column for week number? Then you can filter it, sort it, use a Pivot Table to analyse it, create Charts to present the data graphically, etc.

None of which is easy if you have an ever growing number of weekly sheets ... which also runs out of steam at the end of the year.

Regards, TMS

[:) Sixthsense :)]

Like what TMS suggested, keep a Master data sheet to hold all your data and based on that create a Pivot Table to see your desired outcome.