Index and match accross multiple sheets

Hi guys

I have two sheets which are identical (apart from the numbers contained within them) which I need transferring over to an “upload” sheet.

On the upload sheet I require it to list all sales/costs. I require a formula to look at sheet “1” and list all the occurrences of values listed in columns Q:S. Once is has listed all the values I then need it to list the same (all the occurrences of values listed in columns Q:S) but this time on sheet “2”.

I have uploaded an example of the sheet along with a example of what I require as I fear I have not explained this at all that well.

Can anyone help with the formula please?

Regards

Paul

Yes of course, this wouldn't be a problem.

Pl see the attached file with formulas in columns C to G of "Upload" sheet.
No helper columns.All formulas can be dragged down.

Hi,

Thank you both for the very helpful replies - both methods work brilliantly!

Due to simplicity I have decided to use kvsrinivasamurthy's method but I have a couple of questions:

Is there any way this can be amended:

Please Login or Register  to view this content.

So rather than me having to retype what columns to return e.g. Jan would be column Q as above, Feb would be R and March would be S as per the following:

Please Login or Register  to view this content.

Could I include another formula in which it matches the month (with row 4 on the upload sheet and row 4 of the other sheets), is that possible?


Another question I have is if I need to include an additional sheet for it to pull the data from how do I amend the formula? There will be a max of 6 sheets in total I need it to pull the data from, the example I uploaded only had two. I am unsure how to amend the formula to include the additional formula to pull data from the other sheets.

Pl upload the dummy file in the original format and dummy data, With the way how the result should be.

Hi,

Please see the attached. This is the exact file I will be using

I have entered some dummy data into it and provided an example on the upload sheet of what should be returned as a result

Thank you for looking

Hi,

Can anyone help with adapting these formulas?

This can be achieved from Macros.Is it suitable for you.

Hi,

Using a macro wouldnt be a problem at all

Pl see the file with Macro

Hi,

I made an error in the example I needed. I need it to pull the "Primary" key as well as the GL and the amount.

On the example file you posted it did pull the Primary key but not in this macro.

I have re-uploaded the file with an example of what I need, sorry about that!!

Is there any way I can have the macro not return the results if it doesn’t have a primary key. e.g. Total sales is returned in the macro but I would like this ignore as its only a subtotal.

For reference: I am referring to a primary key - this isn’t actually a primary key - in reality this a cost centre but for ease in the dummy file I used consecutive numbers.

@Fortis The “Primary” key can be any number, the GL column is identical on each of the sheets 1-6 so they will not change. E.g. 212011 will appear on all 6 sheets. On the upload sheet I would only need 212011 to appear if there is a number on any of the sheets 1-6 in the month headings (Jan-March in this example) and I would need the Primary (Column A) and GL (Column G) returned.

e.g.
If the sheets 1-6 were blank and a value of 50 was entered in S6 on sheet two I would need the upload sheet to show:
50373 212011 Sales in Cnt to PPE and 50 under the heading January

Just to be clear - if 212011 appears on two sheets I would need them listed twice If they have different Primary keys. e.g.

If £50 was entered on "1" under January and £200 was entered on "3" under March, the upload sheet should look like this:
50001 212011 Sales in Cnt to PPE, 50, Blank, Blank
50745 212011 Sales in Cnt to PPE, Blank, Blank, 200

At the moment I think the macro is ignoring the primary key and summing the GL code

Pl see the attached file with changed Macro.

Hi,

Thank you so much for this - I have been testing it today and everything so far looks good!

I do have a question regarding the code:

Please Login or Register  to view this content.

If I was to add a column "April 2013" on each of the sheets "1-6" in column X how would I change the code if I require the months February, March, April to be summarised? The plan is to have to do this at least monthly with a rolling forecast.

Paul

You can add April 2013.
Search takes on Period number(Eg Period 10 for S column of other sheets) given in the 5th Row of Upload Column.
In your file Period 10 is taken from Upload sheet and searced in Sheet '1' ,that column data is transfered from all sheets to "Upload" sheet.No change of code required.
For summarising take sum at the end.

Hi,

I must be doing something wrong. On the attached I have added column X on each of the sheets.

Then on the upload sheet I have changed the Periods and Month headings however it doesn’t pull the data.

Any ideas why?

One thing which may cause problems is that there will be duplication of periods - e.g. by me adding this new column there are two instances of period 1 (April 12 and April 13)

Your guess is ok .There should not be duplication of Period numbers. Change the period number and try.

Hi,

Unfortunately I still cannot get it to pull the correct data.

In the attached I deleted duplicate period numbers on sheets 1:6 but it still will not pick up Aprils information.

Why would this be?

Pl change these lines

Please Login or Register  to view this content.

into this

Please Login or Register  to view this content.

It searches for date instead of Period.

Thank you kvsrinivasamurthy

This now works brilliantly! You have solved a massive problem for me - it will save me hours of time!

I will mark this post as solved.