Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
Hi,
I've been trying to solve a problem using functions and could use some insight. The trouble is worksheet_A names are a smaller subset of names contained in worksheet_B. I'm trying to match the directory path from worksheet_B to their respective names on worksheet_A. I'm trying to do this since worksheet A contains 10000 items and B contains 20000 items. Far too tedious for manual work. The scenario: Worksheet_A: Column A: name. Using formula =MATCH(P2,Worksheet_B!$A$2:$A$50,0) Column B: Check if current cell contains N/A. Using formula =ISNA(A2). Column C: Since array on Worksheet_B is offset by 1 due to header, add 1 to Column A. Using formula =A2+1. Column D: Combine letter B with column C to get other sheet reference cell. Using formula =CONCATENATE("B",C2) Column E: Combine worksheet B name with cell reference to get full reference. Using formula =CONCATENATE("worksheet_B!",D2) Column F: This is where I'm stuck. I'm trying to use column E value to return the actual value in column B on worksheet_B. Worksheet_B: Column A: Contains array of names. Column B: Contains a directory path. I hope this makes sense. Any help is appreciated. Thanks, Gun |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Reference
Didn't finish example, in this case, worksheet_A column P would be
the subset list of names. On Feb 13, 4:10 pm, "Gunhaver_06" wrote: Hi, I've been trying to solve a problem using functions and could use some insight. The trouble is worksheet_A names are a smaller subset of names contained in worksheet_B. I'm trying to match the directory path from worksheet_B to their respective names on worksheet_A. I'm trying to do this since worksheet A contains 10000 items and B contains 20000 items. Far too tedious for manual work. The scenario: Worksheet_A: Column A: name. Using formula =MATCH(P2,Worksheet_B!$A$2:$A$50,0) Column B: Check if current cell contains N/A. Using formula =ISNA(A2). Column C: Since array on Worksheet_B is offset by 1 due to header, add 1 to Column A. Using formula =A2+1. Column D: Combine letter B with column C to get other sheet reference cell. Using formula =CONCATENATE("B",C2) Column E: Combine worksheet B name with cell reference to get full reference. Using formula =CONCATENATE("worksheet_B!",D2) Column F: This is where I'm stuck. I'm trying to use column E value to return the actual value in column B on worksheet_B. Worksheet_B: Column A: Contains array of names. Column B: Contains a directory path. I hope this makes sense. Any help is appreciated. Thanks, Gun |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |