Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003 FORMULA only plz -input in cell, chng & go to cell on otr wks
I am in need of assistance to create a find and go to FORMULA with bells and
whistles. When partial or full info is entered into sheet/cell "Calc.!A7", it will automatically locate the matching information located on several worksheets of 1 workbook. Then it will highlight and bold those cells and the corresponding row of cells. I know this is going to be a multiple step/page formula(s), a macro would save lot's of time, however, for this project, I can not use the VB/VBA/MACRO option. Long story short, "TOO LATE"... Essentially, entering text in the "P/N" in "Calc.!A7", these formulas would go to work; indicating the corresponding cells values starting within the calc sheet, (P/N, Height, Width, Length, Pcs/Box, PRICE/PC) and so on, in all of the worksheets via highlighting and bolding the text, if no match return "item not found" If match found, fill corresponding info into the WRU sheet according to correct column and row. Repeating the same actions when text entered in "Calc.!A8" I have tried to indicate the cell identification via (**) next to or above each entry. Each new line indicates the next set of cells down. Sheet1- "Calc." (A7) (B7)<1ST P/N SEARCH BOX. ENTER DESIRED PART# HERE. (A8) (B8)<2ND P/N SEARCH BOX. ENTER DESIRED PART# HERE. P/N (A9) Height Width Length Pcs/Box PRICE/PC (A12) (B12) (C12) (D12) (E12) (F12) ASA RMP 11612 1/16 1 2 1000 $1.01 ASA RMP 11614 1/16 1 4 1000 $1.02 ASA RMP 1161182 1/16 1 1/8 2 1000 $1.03 ASA RMP 1161184 1/16 1 1/8 4 1000 $1.04 ASA RMP 1812 1/8 1 2 1000 $1.05 ASA RMP 1814 1/8 1 4 1000 $1.06 ASA RMP 181182 1/8 1 1/8 2 1000 $1.07 ASA RMP 181184 1/8 1 1/8 4 1000 $1.08 ASA RMP 3161182 3/16 1 1/8 2 1000 $1.09 ASA RMP 3161184 3/16 1 1/8 4 1000 $1.10 ASA RMP 1412 1/4 1 2 500 $1.11 ASA RMP 1414 1/4 1 4 500 $1.12 ASA RMP 141182 1/4 1 1/8 2 500 $1.13 ASA RMP 141184 1/4 1 1/8 4 500 $1.14 Sheet2 - "WU" - generally, has the same info, except with more detail and lines do not match up sheet to sheet. Sheet3 - "QTS" -here again, generally has the same as 1 & 2 but, with more detail and lines do not match up sheet to sheet. Sheet4 - "WRU" sample; (A9) (B9) SOLD TO ABC COMPANY 88999 FRUSTRATED RD. EXCEL HELL, CA 95540 (951) 204-7585 (A13) (B13) SHIP TO ABC COMPANY - S 121211 PLEASE HELP RD EXCEL HELL, CA 95632 (A17) (H17) (I17) PART NO. / DESCRIPTION QUANTITY UNIT PRICE P/N: ASA RMP 181184 2000 PCS $10.10/MPCS WIDGET SMALL ORANGE 0.125" X 1.125" CUT @ 4" (E37) (F37) (H37) (I37) 2 CASES, 1000 PCS PER CASE Your expertise and advise is greatly appreciated! Thank you. -- Ever so greatful, tlc |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2003 FORMULA only plz -input in cell, chng & go to cell on otr wks
Formulas only make calculations and return the results to the cell they are
entered in, the do not execute commands on the interface, so they can not go to other files and format cells. You might do some of what you want using conditional formatting. But unfortunately your descriptions sound like what macros do. -- If this helps, please click the Yes button Cheers, Shane Devenshire "tlc" wrote: I am in need of assistance to create a find and go to FORMULA with bells and whistles. When partial or full info is entered into sheet/cell "Calc.!A7", it will automatically locate the matching information located on several worksheets of 1 workbook. Then it will highlight and bold those cells and the corresponding row of cells. I know this is going to be a multiple step/page formula(s), a macro would save lot's of time, however, for this project, I can not use the VB/VBA/MACRO option. Long story short, "TOO LATE"... Essentially, entering text in the "P/N" in "Calc.!A7", these formulas would go to work; indicating the corresponding cells values starting within the calc sheet, (P/N, Height, Width, Length, Pcs/Box, PRICE/PC) and so on, in all of the worksheets via highlighting and bolding the text, if no match return "item not found" If match found, fill corresponding info into the WRU sheet according to correct column and row. Repeating the same actions when text entered in "Calc.!A8" I have tried to indicate the cell identification via (**) next to or above each entry. Each new line indicates the next set of cells down. Sheet1- "Calc." (A7) (B7)<1ST P/N SEARCH BOX. ENTER DESIRED PART# HERE. (A8) (B8)<2ND P/N SEARCH BOX. ENTER DESIRED PART# HERE. P/N (A9) Height Width Length Pcs/Box PRICE/PC (A12) (B12) (C12) (D12) (E12) (F12) ASA RMP 11612 1/16 1 2 1000 $1.01 ASA RMP 11614 1/16 1 4 1000 $1.02 ASA RMP 1161182 1/16 1 1/8 2 1000 $1.03 ASA RMP 1161184 1/16 1 1/8 4 1000 $1.04 ASA RMP 1812 1/8 1 2 1000 $1.05 ASA RMP 1814 1/8 1 4 1000 $1.06 ASA RMP 181182 1/8 1 1/8 2 1000 $1.07 ASA RMP 181184 1/8 1 1/8 4 1000 $1.08 ASA RMP 3161182 3/16 1 1/8 2 1000 $1.09 ASA RMP 3161184 3/16 1 1/8 4 1000 $1.10 ASA RMP 1412 1/4 1 2 500 $1.11 ASA RMP 1414 1/4 1 4 500 $1.12 ASA RMP 141182 1/4 1 1/8 2 500 $1.13 ASA RMP 141184 1/4 1 1/8 4 500 $1.14 Sheet2 - "WU" - generally, has the same info, except with more detail and lines do not match up sheet to sheet. Sheet3 - "QTS" -here again, generally has the same as 1 & 2 but, with more detail and lines do not match up sheet to sheet. Sheet4 - "WRU" sample; (A9) (B9) SOLD TO ABC COMPANY 88999 FRUSTRATED RD. EXCEL HELL, CA 95540 (951) 204-7585 (A13) (B13) SHIP TO ABC COMPANY - S 121211 PLEASE HELP RD EXCEL HELL, CA 95632 (A17) (H17) (I17) PART NO. / DESCRIPTION QUANTITY UNIT PRICE P/N: ASA RMP 181184 2000 PCS $10.10/MPCS WIDGET SMALL ORANGE 0.125" X 1.125" CUT @ 4" (E37) (F37) (H37) (I37) 2 CASES, 1000 PCS PER CASE Your expertise and advise is greatly appreciated! Thank you. -- Ever so greatful, tlc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
B29 HAS JB# THATS IN OTHER CELLS IF I CHNG B29 WANT ALL TO CHNG? | New Users to Excel | |||
Cell input into formula | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Cell Formula or Cell Input | Excel Worksheet Functions | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) |