programmatic vlookup on "summary" sheet on save

Taking this project a step further, I quickly ran into another small problem. I can fix it with a macro but I dont like the slowness of it and would like to do this programmatically.

so i have attached the file that Tiger helped me with earlier. Here is what I am looking to do.

sheet 1 is the entry sheet.
sheet 2 is the lookup sheet.
sheet 3 is the datatbl sheet.

On sheet 1 you enter your data, after completing a row of data I want it to programmatically (maybe on save?? maybe on close?? I could even tie it to a button click event if it takes a long time) count the total number of rows on sheet 1 and do VLOOKUP's and references (one per row) on the datatbl sheet. This data will be copied and pasted special (values only) over the top of itself (ugly maybe ... but I dont know a better way and i have the code for it). I could hard code these on the datatbl and assume like 10000 rows, but then every save when it refreshes it would take forever. Because the ability at anytime to INSERT a new row at any point on the entry sheet is possible, the vlookup would have to do a refresh to make sure no rows got missed.

Am I making this harder than needs be I just want it to (on some event) count the rows on entry sheet and put the corresponding data into the datatbl using the vlookups and references as shown.

Please see the datatbl sheet to see what I need for each row programmatically.

-EDIT: The reason for all of this is because I want to be able to IMPORT the lookup values of the text (ex/ 3001 instead of the word CARROT), because this is being imported into an Access Database that uses these IDENTIFIERS instead of the words. So I need the entry sheet to be user friendly where they can select the text and then have a hidden sheet (lookup tbl) that contains the ID's of these values and on some event have all the rows from the entry sheet get "changed" into their ID numbers and loaded onto the datatbl. One per row. Easy right??? :-)

See attached:
1) I added a sheet called 'cell-addr_fx' that uses the cell() formula to put all the necessary cell address for the Entry sheet in these cells.
2) I then copy-values only this sheet to 'cell-addr_values'. Now if you add a row to Entry, then the cell values in this sheet never change.
3) I then edited your vlookup formulas to include the INDIRECT() formula inside of the VLOOKUP() formula.
4) I copied more rows down in the datatbl sheet to see if this new formula copies ok, and it does. NOTE: The VLOOKUP() formula in column 'E' did NOT have absolute addressing for the LOOKUP table, which it needs, so I corrected this.
5) Note: Essentially the 'cell-addr_fx' sheet is no longer used anymore. Once you have the workbook fully built-up, this sheet could be deleted.

Everything seems to work, I think, per your description. And, no macros required(). The trick here is using the INDIRECT() formula and using it to read hard values that contain text referring to the 'Entry' sheet cells. This way you can insert rows in 'Entry', and these hard values (the copy-values only cells) will never change. So, the INDIRECT() formula sitting in 'datatbl!A2', for example, will always look at 'Entry!A2'. ... SUGGESTION: The full array of Lookup Formulas (& there are a lot of them) are kind of cool to read up on & know what they can do for you; this will come in handy for you, like this situation!

I bet there is even an easier way to do this. Just wait a few hours and one of the forum master-mind-mega-gurus (speaking with the upmost of respect! ... and there's about ~10-15 of them out there), will chime in and show how to do this without the extra two sheets, all in one simple forumula. Essentially, all you really need is a way to TURN OFF the changing of formula addressing (in sheet 'datatbl') that occurs when you move a reference cell. I don't know how to do this without the shenanigans that I describe above, but this does work & it isn't too ugly, but look forward to learning a better way! ... Regards!

See Ver4 attached file. Changes include:
1) Altered equations in cell-addr_fx so to remove problem with name of file not matching the hard values in the cell-addr_value cells. This also makes these cells a lot easier to read.
2) Altered all equations in datatbl sheet so that NONE of the columns (including the non-lookup formula cells) get messed up if you insert rows to the Entry sheet.
3) Added additional provisions to the datatbl formulas so to show NOTHING in cell if the corresponding Entry cell is empty.
4) Copied the cell-addr sheets down to row 100 & copied the datatbl formulas down to row 100.

Try it out: Add a couple rows to Entry and it should work for you. If you need datatbl to go to row 10,000, then copy formulas in row1 of cell-addr_fx down to row 10,000, then copy values-only the entire cell-addr_fx sheet on top of the cell-addr_values sheet. Lastly, copy the formulas in datatbl (any row) down to row 10,000. You only have to do this 1 time! After this, the formulas will work for you very fast. No need for any macros. Simply make changes to Entry and the datatbl will automatically update immediately.

NOTE: If you do add rows to Entry, then, after that, if want to copy (values only) the formulas in cell-addr_fx over to cell-ddr_values, you first need to "unshift" these formulas as adding rows to Entry will cause the formula references in cell-addr_fx to "shift" which you don't want. To "unshift", simply copy the formula in A1 (cell-addr_fx) down to its F10000, and this will line the formulas back up 1:1 for each cell. Regards!