Retrieving data from one Excel sheet to another (More like amkig Access like queries in excel)

[zaidpirwani]

Hi,

I have a database of 338 members, all the information is entered in an excel sheet (named: Main) column wise, lets suppose the data for the first member is in the row 2 (first row is used for column headers)
i.e.:
A1 contains Serial (Here 1)
B1 contains First Name (Here John)
C1 contains Last Name (Here Doe)
D1 contains Address (Here Somewhere in America)
and so on.

Now I have created another work sheet which is well formatted and I have designed it in such a way that it looks like a summary pager for every individual member, now right now I had to make 338 sheets for each member and had to put like ="MEMBERSHIP # 00" & Main!A6 in a cell to show the membership number and =Main!B6 & " " & Main!C6 to show the persons full name, like this I have created 338 sheets and the only difference in the formulas of these sheets is the row number. Nothing else is changed.

I was wondering if there was any method to do all this without 338 sheets and I would only had to make a single sheet with the formulas and would just need to type in the row number in a certain cell which would rewrite all the formulas so they would point to that row. I mean like making a query like structure (the one made in access where you can define a statement Show * from Main Where SNo=1).

Any kind of help or tips in this regard will be highly appreciated, I know much about excel but have been trying to figure this out for a long time now, I think one way would be to make Macro, but I am weak in Macros and don't know where to start.

Thanks in advance for your help.

 

[jindon]

Enter Row number in A1

B1:
=Indirect(Address($F1,Column(A1),,,"main"))
then fill right

 

[lalbatros]

zaidpirwani,

You could also try to use a PivotTable or a Database Query.

With the PivotTable, you can create additional formulas and make many different reports in less time than it takes to explain how.

To create a database query, save your excel file first.
Then go to the "Data/Inport External Data/New Database Query" menu and follow the track.
This solution is a bit less confortable because it will not work anymore if you move your file to another place since Excel access the data by following the given file path. However, it is possible to refresh the path.

Both solutions have at least two big advantage: 1) no formulas and 2) the returned result occupies a variable size range.

 

[zaidpirwani]

Enter Row number in A1

B1:
=Indirect(Address($F1,Column(A1),,,"main"))
then fill right

Could you please be more specific; what do I do in F1

="MEMBERSHIP # 00" & Main!A6

Please take the above formula as a sample; This is written in A2 of Sheet2, and Main is another sheet; A6 in Main is the Row from which data is retrieved, I want a formula which will change the 6 of A6 in this formula to the number which I will type in A1 of Sheet2.

Thanks, am googling the function INDIRECT right now.

 

[jindon]

OOps

Enter Row number in A1

A2:
=Rept("MEMBERSHIP # 00",Column(A1)=6)&Indirect(Address($A1,Column(A1),,,"main"))
then fill right

 

[zaidpirwani]

Thanks man, I got it and it works, It just works

Thanks to all in this Thread.