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
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
