Excel or Access for multiple table look ups

Nieriel

New Member
Joined
Feb 16, 2015
Messages
26
Hi All,
Hoping some advice as I am not sure whether to use Excel or Access (I'm better with Excel than Access) to auto populate a cell based on multiple tables. I'll explain how I thought it may work in excel - I just want to see if this would be easier/better in Access or indeed if there is a better way to do it in excel (or if it's just going to be too complicated full stop)

I have a form in excel, there are 4 cells that the user populates (all from drop down lists) on 4 different rows but same column, for this example I'll use Column A, based on the below I want to return a value in Cell A5
If cell A1 contains data but the other 3 are blank I want it to run an index match search on column 1 in table 1 and return the value in column 2
If cell A1 and A2 contain data but the other 2 are blank an index search needs to be ran on table 2 however this table is in a grid format, so values of Cell A1 in rows and A2 across the top - I am planning on using an index match for this, A1 and A3 would be a different table (A3 would be across the columns in this one) A1, A2 and A3 would be a different table IF the values in A2 and A3 were different (if they were the same it would use the same table as if A3 was blank), this table would have A3 in columns and A1 in rows. These are the only 2 values that can potentially match, all the others would just need to be if the cell was/wasn't blank.

There will be at least 8 tables as there can be data in all 4 cells or any combination of cells i.e A1 and A4/A1 and A3 or just A1 on it's own - none of the others will be on their own as there will always be data in A1.

I have started to populate the tables which are not exactly small - each one is at least 200 rows x 100 columns.

I am planning on using a nested IF statement in Cell A5 to cycle through all the possibilities however just playing around today it was getting rather lengthy and awkward to follow to see which possible combinations I had covered.

I though about Access - the value in A1 will be on every table however all the Access I've done is to pull different data together based on a linked field on each table - I don't want to do that I need some form of formula or macro to look at a specific table independently based on the possible combinations and values in cells A1-A4

Does the above sound viable? Is there any better way than nested IF statements?
Unfortunately I am not much use with VBA - I can edit and manipulate code already written to get it to work on things I've needed in the past but I cannot write code from scratch!

Any advice appreciated,
Thanks
N
 

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
802
Yes the formulas would get lengthy trying to put in so many variables.

One thought is you could have a cell on each sheet, or near each table, that references the first sheet's criteria.
So if for example the first cell is the only one with data, the the formulas on the other sheets, or near the other tables, would get a zero unless it met their criteria.

Then your formula from your first sheet could look at the answers of each sheet and take the highest answer, because the other sheets would get a zero if they don't comply.
 

Forum statistics

Threads
1,081,973
Messages
5,362,505
Members
400,677
Latest member
champchamp

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