Formula to drill down into several sheets and look for a specific figures?

[YAbdelaal]

Hi guys, I'm trying to design a summary sheet for an inventory costing model, and need some kind of complex formula for this.

For the sake of simplicity, let's say I have my sheets named as follows: Item1, Item2, Item3, etc...

On the summary sheet, I have the following:

	Jun-14	Jul-14
Item1
Item2
Item3
Item4

Now, I need to put a formula in the Jun and Jul columns, to check whether Cell B3 on every sheet matches with the Item#. If it does, I need it to check column B for the date... if that matches, then it should return the value in the cell two columns to the right and 15 rows down (offset?).

Can somebody help, please?

Thanks in advance!

[rcm]

Why do you keep a tab for every item? Could you send an excel sample of the tab layout?

[YAbdelaal]

Thanks for replying so quickly!

I have to keep a tab for every item because each tab has a running monthly costing calculation (opening + purchases = available for sale - ending = usage)

Here is a sample.

Basically, I need it to match the name in B3 with the summary, and then check the date in column B (May in B5, June in June 28, and return the variance (Cell D22) for May, June, July, etc...

[rcm]

look at the hoja1 tab

[YAbdelaal]

look at the hoja1 tab

That's great! It works, but not for all the items, for some reason. Actually, it seems to not work for tabs where the name of the sheet has a space in it. How can this be fixed?

[rcm]

Ill fix it. it needs quotation marks

[rcm]

this should do it

[YAbdelaal]

this should do it

Hey, thanks a lot for your help. Unfortunately, it still doesn't work What I'm trying to do is have the sheet be named Item 1 instead of Item1 and still have the formula be able to pick it up, but that's not the case.

Any other ideas?

[rcm]

Please Login or Register  to view this content.

I forgot about the second reference, NOW TRY IT

[YAbdelaal]

Please Login or Register  to view this content.

I forgot about the second reference, NOW TRY IT

Sorry, I'm really confused now. The formulas has completely changed? What's SI.ERROR and INDIRECTO? Either way, I've plugged it in and it gives me #NAME? error.

[YAbdelaal]

Please Login or Register  to view this content.

I forgot about the second reference, NOW TRY IT

Sorry, I'm really confused now. The formulas has completely changed? What's SI.ERROR and INDIRECTO? Either way, I've plugged it in and it gives me #NAME? error.

[YAbdelaal]

Please Login or Register  to view this content.

I forgot about the second reference, NOW TRY IT

Sorry, double post

[rcm]

Please Login or Register  to view this content.

nothing has changed, I did not translate back into english (I am running excel in spanish). the only addition are the quotes in the second indirect reference...

[YAbdelaal]

Please Login or Register  to view this content.

nothing has changed, I did not translate back into english (I am running excel in spanish). the only addition are the quotes in the second indirect reference...

Wow, I didn't even know the formulas were different in different languages! Anyway, it works perfectly now. Thank you so much!

[rcm]

You are welcome. I kept you waiting and wondering but at the end it worked... Marke your post as solved