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

zaidpirwani

New Member
Joined
Oct 30, 2008
Messages
3
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Enter Row number in A1

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

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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.
 
Last edited:

zaidpirwani

New Member
Joined
Oct 30, 2008
Messages
3
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OOps

Enter Row number in A1

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

Forum statistics

Threads
1,081,902
Messages
5,361,964
Members
400,667
Latest member
cryptomike

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top