Dear helpful people of Mr. Excel,
I have been searching for an answer to this problem for quite some time now, to no avail.
I have an Excel 2010 workbook, which keeps track of all the supplies needed for production at my store.
Using a VLookup formula, which is applied to every cell in Column C-Column Q in a sheet called "USED", I am able to populate an entire row, with data from a table in a second sheet called "Sheet 4". This runs when I enter a code word in any cell of Column B.
This works fine, but since "USED" has a formula on so many cells, excel runs slowly whenever opening the workbook, entering data, or saving.
What I am looking for is a way to apply the formula to all the appropriate cells in Columns C-Q in sheet "USED" without putting the formula in all the actual cells themselves.
I believe I can use a VBA to automatically detect when I enter a code into Column B of "USED", and then automatically fill the appropriate data from "Sheet 4" into the appropriate row of "USED". I just can't seem to figure out how!
I am not sure how to attach my Workbook as the "HTML-Maker" section of the FAQ is confusing,
so here is some important data:
Formulas applied respectively to Columns C-Q in sheet "USED"*:
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,2,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,3,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,4,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,5,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,6,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,7,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,8,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,9,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,10,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,11,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,12,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,13,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,14,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,15,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,16,FALSE),"")
*Since each row will change the number of Column B,
"B2" will change to "B3", B4", B5", etc as the row changes.
I hope this was clear enough, and look forward to the solution!
Warm Regards,
Nate Zona
I have been searching for an answer to this problem for quite some time now, to no avail.
I have an Excel 2010 workbook, which keeps track of all the supplies needed for production at my store.
Using a VLookup formula, which is applied to every cell in Column C-Column Q in a sheet called "USED", I am able to populate an entire row, with data from a table in a second sheet called "Sheet 4". This runs when I enter a code word in any cell of Column B.
This works fine, but since "USED" has a formula on so many cells, excel runs slowly whenever opening the workbook, entering data, or saving.
What I am looking for is a way to apply the formula to all the appropriate cells in Columns C-Q in sheet "USED" without putting the formula in all the actual cells themselves.
I believe I can use a VBA to automatically detect when I enter a code into Column B of "USED", and then automatically fill the appropriate data from "Sheet 4" into the appropriate row of "USED". I just can't seem to figure out how!
I am not sure how to attach my Workbook as the "HTML-Maker" section of the FAQ is confusing,
so here is some important data:
Formulas applied respectively to Columns C-Q in sheet "USED"*:
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,2,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,3,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,4,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,5,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,6,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,7,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,8,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,9,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,10,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,11,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,12,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,13,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,14,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,15,FALSE),"")
=IFERROR(VLOOKUP(USED!B2,Sheet4!$B$16:$Q$18,16,FALSE),"")
*Since each row will change the number of Column B,
"B2" will change to "B3", B4", B5", etc as the row changes.
I hope this was clear enough, and look forward to the solution!
Warm Regards,
Nate Zona