Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
We use Office Excel 2003- We need to insert data from preset column B in
Spreadsheet 1 to "new column B" in Spreadsheet 2 (downloaded data in Col A, but new Col B created by us). Thus, the formula you advise for cells in Sheet 2 column B, need to produce the result x=Medium, y=Small and Z=large, based on naming criteria match. Sheet 1 Sheet 2 Col A Col B Col A Col B FundA Large Fund B x FundB Medium Fund C y FundC Small Fund A z |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
=IF('Sheet1'!B1="Large","x",IF('Sheet1'!B1="Medium ","y",IF('Sheet1'!B1="Small","z","Some other entry was present")))
Then you can just copy and paste down. -- Best Regards, Luke M "Fundman" wrote: We use Office Excel 2003- We need to insert data from preset column B in Spreadsheet 1 to "new column B" in Spreadsheet 2 (downloaded data in Col A, but new Col B created by us). Thus, the formula you advise for cells in Sheet 2 column B, need to produce the result x=Medium, y=Small and Z=large, based on naming criteria match. Sheet 1 Sheet 2 Col A Col B Col A Col B FundA Large Fund B x FundB Medium Fund C y FundC Small Fund A z |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
Unfortunately, this did not totally answer the question.
The formula for Sheet2 Col B Rows1-3, needs to look at 2 variables. First, it has to recognize the "Cell-Fund B in Sheet2 Col A". It then has to look to match this text to a cell in "Sheet1 Col A" (in this case, the row 2). Once it finds that match, it needs to insert the result "medium" in Sheet2, ColumnB, Row 1. Once you suggest the formula, we confirm that it would be copied and pasted down. We look forward to your new suggested solution...Thanks again Luke in advance "Luke M" wrote: =IF('Sheet1'!B1="Large","x",IF('Sheet1'!B1="Medium ","y",IF('Sheet1'!B1="Small","z","Some other entry was present"))) Then you can just copy and paste down. -- Best Regards, Luke M "Fundman" wrote: We use Office Excel 2003- We need to insert data from preset column B in Spreadsheet 1 to "new column B" in Spreadsheet 2 (downloaded data in Col A, but new Col B created by us). Thus, the formula you advise for cells in Sheet 2 column B, need to produce the result x=Medium, y=Small and Z=large, based on naming criteria match. Sheet 1 Sheet 2 Col A Col B Col A Col B FundA Large Fund B x FundB Medium Fund C y FundC Small Fund A z |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
Think a simple vlookup should do work fine
Source data assumed in Sheet1, cols A & B In Sheet2, Place in B1: =IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0)) Copy down as far as required If the above vlookup doesn't match/return 100%, that means you've got a data consistency issue in the fund names in Sheet1/2's col A - ie they don't exactly match If so, you could try this fuzzy, heavier duty index/match .. Place instead in B1, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(A1="","",INDEX(Sheet1!$B$1:$B$100,MATCH(TRUE,I SNUMBER(SEARCH(A1,Sheet1!$A$1:$A$100)),0))) Copy down as far as required. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fundman" wrote: Unfortunately, this did not totally answer the question. The formula for Sheet2 Col B Rows1-3, needs to look at 2 variables. First, it has to recognize the "Cell-Fund B in Sheet2 Col A". It then has to look to match this text to a cell in "Sheet1 Col A" (in this case, the row 2). Once it finds that match, it needs to insert the result "medium" in Sheet2, ColumnB, Row 1. Once you suggest the formula, we confirm that it would be copied and pasted down. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
Max-
When I inserted the formula you suggested I got FALSE each time. Perhaps I don't understand what needs to be input in the quotes for the first part of the argument A1="","" However, when I move the sheet 1 legend (columns a and b or a total of 6 cells) into bottom of Sheet 2 and just use the function VLOOKUP ($E39,$E39:$F41,2,0) it seems to produce the correct text in column b for each of the 3 arguments. ..Any final thoughts? Thanks again.. "Max" wrote: Think a simple vlookup should do work fine Source data assumed in Sheet1, cols A & B In Sheet2, Place in B1: =IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0)) Copy down as far as required If the above vlookup doesn't match/return 100%, that means you've got a data consistency issue in the fund names in Sheet1/2's col A - ie they don't exactly match If so, you could try this fuzzy, heavier duty index/match .. Place instead in B1, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(A1="","",INDEX(Sheet1!$B$1:$B$100,MATCH(TRUE,I SNUMBER(SEARCH(A1,Sheet1!$A$1:$A$100)),0))) Copy down as far as required. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fundman" wrote: Unfortunately, this did not totally answer the question. The formula for Sheet2 Col B Rows1-3, needs to look at 2 variables. First, it has to recognize the "Cell-Fund B in Sheet2 Col A". It then has to look to match this text to a cell in "Sheet1 Col A" (in this case, the row 2). Once it finds that match, it needs to insert the result "medium" in Sheet2, ColumnB, Row 1. Once you suggest the formula, we confirm that it would be copied and pasted down. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
I'm not sure what's happening over there ..
Perhaps a working sample to illustrate the earlier suggestion would be good?: http://cjoint.com/?mqxdgOOOpw Vlookup_example.xls The suggested expression: =IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0)) presumes that your source data/table is in Sheet1's col A and B and your lookup values in Sheet2 starts in A1 down The front IF part of the expression: =IF(A1="","", .. is simply to check whether the lookup col A is "blank" If it is, then the expression will return neat looking blanks: "" So the formula could be copied down beyond existing data in col A (beyond the last data row in col A) to cater for future data entries -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fundman" wrote: Max- When I inserted the formula you suggested I got FALSE each time. Perhaps I don't understand what needs to be input in the quotes for the first part of the argument A1="","" However, when I move the sheet 1 legend (columns a and b or a total of 6 cells) into bottom of Sheet 2 and just use the function VLOOKUP ($E39,$E39:$F41,2,0) it seems to produce the correct text in column b for each of the 3 arguments. ..Any final thoughts? Thanks again.. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
Works now...Thanks Max
"Max" wrote: I'm not sure what's happening over there .. Perhaps a working sample to illustrate the earlier suggestion would be good?: http://cjoint.com/?mqxdgOOOpw Vlookup_example.xls The suggested expression: =IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0)) presumes that your source data/table is in Sheet1's col A and B and your lookup values in Sheet2 starts in A1 down The front IF part of the expression: =IF(A1="","", .. is simply to check whether the lookup col A is "blank" If it is, then the expression will return neat looking blanks: "" So the formula could be copied down beyond existing data in col A (beyond the last data row in col A) to cater for future data entries -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fundman" wrote: Max- When I inserted the formula you suggested I got FALSE each time. Perhaps I don't understand what needs to be input in the quotes for the first part of the argument A1="","" However, when I move the sheet 1 legend (columns a and b or a total of 6 cells) into bottom of Sheet 2 and just use the function VLOOKUP ($E39,$E39:$F41,2,0) it seems to produce the correct text in column b for each of the 3 arguments. ..Any final thoughts? Thanks again.. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Cell fornula or format
Glad that cleared it up for you.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fundman" wrote in message ... Works now...Thanks Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Can cell format come from and change with reference cell format | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
Change over time fornula | Excel Worksheet Functions | |||
How do I copy data in single cell format to a merged cell format | Excel Discussion (Misc queries) |