Getting different formula outputs for different given inputs

Hi all,

I have a worksheet I'll call TheFormulasheet for now. In this sheet cell A50 eventually gives an output based on a load of formulae and reference tables, and based on the values put in A10, A11, and A12. For multiple reasons it is not possible to edit anything in TheFormulasheet and it should therefore be considered read-only.

Next to this I also have what I call TheDatabasesheet. It's a basic database with slightly over 20k records (rows), using row 1 as a header row containing field names. Now what I basically want for my records is that column M gives the output of A50 from TheFormulasheet when TheFormulasheet's A10, A11, and A12 are using the values in column X, column Y, and column Z in TheDatabasesheet.

In programming language it would probably sound somewhat like this for the first record in column M:

M2 = TheFormulasheet.A50, for when TheFormulasheet.A10 uses X2, and for when TheFormulasheet.A11 uses Y2, and for when TheFormulasheet.A12 uses Z2.

Then I want to auto copy it down for my 20k records. Due to the complexity of everything happening inside TheFormulasheet it is not possible to simply copy the formula behind A50 and paste it for every record.

I have already tried to Google this but I don't even know what this trick is technically called hence I probably haven't found any useful results yet. How do I pull this off?

Thanks in advance!

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

cant see that

What do you mean, can't see what? The attachment? I can open it fine..

what you describe sounds like a data table but with three inputs which is not possible in excel. your only option I reckon would be a vba routine to perform the substitutions and enter the values in each cell in the database sheet

Is there a more newbie-friendly guide you can direct me to which describes your solution? I have no clue about VBA stuff.

not really-the code would be specific to your situation-but here is a sample of what you would need based upon your example workbook

Please Login or Register  to view this content.

As you can see

no i cant its not clear

However, the actual price calculation of B8 is such a complex formula

well how can i possibly guess what it is?
why not just use the same formula structure in f2 of data?

Originally Posted by martindwilson

why not just use the same formula structure in f2 of data?

Because of the amount of vlookups within different tables in different XLS files it is picking its data from, and because of the fact that should the formula be altered in the future, the database sheets have to be updated manually as well.

Also, in my example I stated 3 input cells and one formula (price calculation), however, the actual situation has 3 input cells and 4 formulae which are all cross-linked and based on each other. Theoretically possible to translate the formulae for every entry in the database, but an awful lot of work!

Currently I'm trying the VBA method to have a macro fill my cells in the database.