How to identify the data based on the column and row headings in different worksheets.

[kadasanis]

HI,

I am very new to excel and VBA Macro's and was trying to prepare a spreadsheet to identify the data based on the column and Row headings.

In sheet1 June i do have list of Students names with their marks and sheet2 July also with the same data in different format(column headings and row headings are different).

So i had to prepare the sheet like the one in Sheet 3,so whenever i give the student name it has to pickup the marks of the student in previous sheets.

There can be 'N' number of sheets with different formats but similar data.

Can i use any Excel functions for this or need an VBA macro for this.

Appreciate any help on this.

[FDibbins]

Hi and welcome to the forum

My 1st question is...why is the data transposed in the 2nd sheet?????

If you have all the data inthe same format, you wont even need different sheets, you can have all the data contained in 1 sheet, andthen use a 2nd sheet to to the summaries.

Extracting the data is the easy part...identifying (on each sheet) which way round the data is, although still easy, will make the formula at least twice as long

[kadasanis]

Thanks for the response.Yes if the sheets were in the same format i would have used a Vlookup function to find the data, but the challenge for me was the data will not be in the same format and in the same cells in each sheet. I just gave you an example picture how it would but not the actual data.So i require a Macro to find out data based on the Cell headings

[FDibbins]

OK Im not sure that you need VBA for this.

1st, fill in the A1's in A8:A13, and the other tables. (you can hide them if you want)

Then copy this down and across...
=IFERROR(IF(LEFT(INDIRECT(C$6&"!B1"),7)="subject",INDEX(INDIRECT(C$6&"!$A$1:$J$10"),MATCH(Sheet3!$A7,INDIRECT(C$6&"!$A$1:$A$10"),0),MATCH(Sheet3!$B7,INDIRECT(C$6&"!$A$1:$J$1"),0)),INDEX(INDIRECT(C$6&"!$A$1:$J$10"),MATCH(Sheet3!$B7,INDIRECT(C$6&"!$A$1:$A$10"),0),MATCH(Sheet3!$A7,INDIRECT(C$6&"!$A$1:$J$1"),0))),"")

I used a range of up to column J and down to row 10 - adjust as needed
Also, I used "subject" as the switch, change that as needed too

[kadasanis]

That was too perfect. I like it.


Thank you sooooooooo much

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)