Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you calculate a cell name? and How to return a value from an adjacent cell?
It's been years since I've really dug into a spreadsheet with lots of
formulas. I've forgotten how much fun it is. I used to be a Lotus 1-2-3 Release 1A wizard. That's how long it's been since I've built spreadsheets with even a medium complexity. So excuse this if these are dumb questions: 1) Can you name a cell with a formula? For instance, let's say there's an column for age. Each cell, I want to name by adding FIRSTNAME + LASTNAME + "AGE". Then I can refer to that cell by name instead of by cell address in later formulas. 2) How do I return a value from column B based on matching a value in column A? For instance, if A is NAME and B is AGE, in a formula, I want to say "get a person's age if NAME is equal to N". Just a function name would suffice. I'm pretty good at figuring things out once I get a pointer. TIA, HK |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you calculate a cell name? and How to return a value from an a
Question 1: You can use named ranges in formulas.
Question 2: = IF(A1="NAME",B1,"") Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "HK" wrote: It's been years since I've really dug into a spreadsheet with lots of formulas. I've forgotten how much fun it is. I used to be a Lotus 1-2-3 Release 1A wizard. That's how long it's been since I've built spreadsheets with even a medium complexity. So excuse this if these are dumb questions: 1) Can you name a cell with a formula? For instance, let's say there's an column for age. Each cell, I want to name by adding FIRSTNAME + LASTNAME + "AGE". Then I can refer to that cell by name instead of by cell address in later formulas. 2) How do I return a value from column B based on matching a value in column A? For instance, if A is NAME and B is AGE, in a formula, I want to say "get a person's age if NAME is equal to N". Just a function name would suffice. I'm pretty good at figuring things out once I get a pointer. TIA, HK |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you calculate a cell name? and How to return a value from an a
Under the Insert menu option there is a name function that will allowing you
to cross reference a name to a value. "HK" wrote: It's been years since I've really dug into a spreadsheet with lots of formulas. I've forgotten how much fun it is. I used to be a Lotus 1-2-3 Release 1A wizard. That's how long it's been since I've built spreadsheets with even a medium complexity. So excuse this if these are dumb questions: 1) Can you name a cell with a formula? For instance, let's say there's an column for age. Each cell, I want to name by adding FIRSTNAME + LASTNAME + "AGE". Then I can refer to that cell by name instead of by cell address in later formulas. 2) How do I return a value from column B based on matching a value in column A? For instance, if A is NAME and B is AGE, in a formula, I want to say "get a person's age if NAME is equal to N". Just a function name would suffice. I'm pretty good at figuring things out once I get a pointer. TIA, HK |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you calculate a cell name? and How to return a value from an a
I want the "NAME" to be derived from a formula. Assuming A1 and B1 are text
values, I want the name of cell C1 to be A1&B1. "Dave F" wrote in message ... Question 1: You can use named ranges in formulas. Question 2: = IF(A1="NAME",B1,"") Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "HK" wrote: It's been years since I've really dug into a spreadsheet with lots of formulas. I've forgotten how much fun it is. I used to be a Lotus 1-2-3 Release 1A wizard. That's how long it's been since I've built spreadsheets with even a medium complexity. So excuse this if these are dumb questions: 1) Can you name a cell with a formula? For instance, let's say there's an column for age. Each cell, I want to name by adding FIRSTNAME + LASTNAME + "AGE". Then I can refer to that cell by name instead of by cell address in later formulas. 2) How do I return a value from column B based on matching a value in column A? For instance, if A is NAME and B is AGE, in a formula, I want to say "get a person's age if NAME is equal to N". Just a function name would suffice. I'm pretty good at figuring things out once I get a pointer. TIA, HK |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you calculate a cell name? and How to return a value from an adjacent cell?
For question 1:
I don;t think you can define a name as per your desires. You actually need a formula: =INDEX(what,MATCH(1,(first="Kostis")*(last="Vezeri des")*(age=45),0)) it is assumed that what, first, last and age are named ranges of the same length (or you can use the actual range references). This is an *array* formula hence it must be commited with Ctrl+Shift+Enter. Question 2 is a simpler version of the multicriteria formula that I gave you: =INDEX(phones,MATCH("myname",names,0)) VLOOKUP is a more compact version of the 2nd formula (only). If you are only referring to a 2-col table (A:B) you can lookup the phone number with: =VLOOKUP("myname",A:B,2,FALSE) HTH Kostis Vezerides On Feb 14, 5:55 pm, "HK" wrote: It's been years since I've really dug into a spreadsheet with lots of formulas. I've forgotten how much fun it is. I used to be a Lotus 1-2-3 Release 1A wizard. That's how long it's been since I've built spreadsheets with even a medium complexity. So excuse this if these are dumb questions: 1) Can you name a cell with a formula? For instance, let's say there's an column for age. Each cell, I want to name by adding FIRSTNAME + LASTNAME + "AGE". Then I can refer to that cell by name instead of by cell address in later formulas. 2) How do I return a value from column B based on matching a value in column A? For instance, if A is NAME and B is AGE, in a formula, I want to say "get a person's age if NAME is equal to N". Just a function name would suffice. I'm pretty good at figuring things out once I get a pointer. TIA, HK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
Return adjacent cell if conditional formatting exists. | Excel Worksheet Functions | |||
Return value in adjacent cell | Excel Worksheet Functions | |||
return value in adjacent cell different workbook | Excel Worksheet Functions | |||
Calculate month-end date from date in adjacent cell? | Excel Worksheet Functions |