| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formula/doesn't pull data with slash (/)
Hi,
I was wondering whether someone could give me a hand with this formula, or whats wrong with this formula: =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product numbers'!E$3:E$500) The formula works, but it doesnt pull the information i want in column e (at the end of the formula). In column E i have three variables: 8400 8500/8550 8400/8500/8550 the formula only returns the 8400 variable, it doesn't seem to like the / , or any other form of punctuation. Please could someone tell me how it can return these variables with their attached /. thank you Greg. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
|
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
=SUMIF (SUM) will ignore the data with / as it treats it as non-numeric.
Do you actual want to SUM these numbers or return them as a string? Given (only) the three cells below, what do you want returned? 8400 8500/8550 8400/8500/8550 "Greg" wrote: Hi, I was wondering whether someone could give me a hand with this formula, or whats wrong with this formula: =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product numbers'!E$3:E$500) The formula works, but it doesnt pull the information i want in column e (at the end of the formula). In column E i have three variables: 8400 8500/8550 8400/8500/8550 the formula only returns the 8400 variable, it doesn't seem to like the / , or any other form of punctuation. Please could someone tell me how it can return these variables with their attached /. thank you Greg. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
Thanks for you reply.
In column E there could be those three variables all the way down and intechangeable eg. E3 = 8400 E4= 8400/8500 E5-= 8400 E6= 8400/8500/8550 etc etc etc So my formula now (the one I wrote before), works. But, it only pulls in 8400. I want it to pull in whatever is in that cell. It doesn't appear to like 8400/8500 or 8400/8500/8550. If it is either one of the two variables with a slash it returns 0. when it should theoretically recall exactly what is in that cell (in the E column). Is that any clearer? Cheers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
I guess I am not really askign it to sum but to pull that specific
cell's entry, so yes the string would be good, as long as it returns exactly what is in that cell. Cheers, Toppers wrote: =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric. Do you actual want to SUM these numbers or return them as a string? Given (only) the three cells below, what do you want returned? 8400 8500/8550 8400/8500/8550 "Greg" wrote: Hi, I was wondering whether someone could give me a hand with this formula, or whats wrong with this formula: =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product numbers'!E$3:E$500) The formula works, but it doesnt pull the information i want in column e (at the end of the formula). In column E i have three variables: 8400 8500/8550 8400/8500/8550 the formula only returns the 8400 variable, it doesn't seem to like the / , or any other form of punctuation. Please could someone tell me how it can return these variables with their attached /. thank you Greg. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
Greg wrote: I guess I am not really askign it to sum but to pull that specific cell's entry, so yes the string would be good, as long as it returns exactly what is in that cell. Cheers, Toppers wrote: =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric. Do you actual want to SUM these numbers or return them as a string? Given (only) the three cells below, what do you want returned? 8400 8500/8550 8400/8500/8550 "Greg" wrote: Hi, I was wondering whether someone could give me a hand with this formula, or whats wrong with this formula: =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product numbers'!E$3:E$500) The formula works, but it doesnt pull the information i want in column e (at the end of the formula). In column E i have three variables: 8400 8500/8550 8400/8500/8550 the formula only returns the 8400 variable, it doesn't seem to like the / , or any other form of punctuation. Please could someone tell me how it can return these variables with their attached /. thank you Greg. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
Try VLOOKUP:
=VLOOKUP(Stock!A32,'Parts&Product Numbers'!D$3:E$500,2,0) Will match A32 against D3:D500 and pull matched value i.e same row, from E3:E500. If there is more than one match, it will only retrieve first one it finds. HTH "Greg" wrote: I guess I am not really askign it to sum but to pull that specific cell's entry, so yes the string would be good, as long as it returns exactly what is in that cell. Cheers, Toppers wrote: =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric. Do you actual want to SUM these numbers or return them as a string? Given (only) the three cells below, what do you want returned? 8400 8500/8550 8400/8500/8550 "Greg" wrote: Hi, I was wondering whether someone could give me a hand with this formula, or whats wrong with this formula: =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product numbers'!E$3:E$500) The formula works, but it doesnt pull the information i want in column e (at the end of the formula). In column E i have three variables: 8400 8500/8550 8400/8500/8550 the formula only returns the 8400 variable, it doesn't seem to like the / , or any other form of punctuation. Please could someone tell me how it can return these variables with their attached /. thank you Greg. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with formula/doesn't pull data with slash (/)
Hi
I have tried that one, and it still doesnt appear to be pulling the cell contents in. It is only pulling in the cells with one specific variant in it, i.e. 8500/8550. It wont pull 2424, or 8400 or 8400/8500/8550 Toppers wrote: Try VLOOKUP: =VLOOKUP(Stock!A32,'Parts&Product Numbers'!D$3:E$500,2,0) Will match A32 against D3:D500 and pull matched value i.e same row, from E3:E500. If there is more than one match, it will only retrieve first one it finds. HTH "Greg" wrote: I guess I am not really askign it to sum but to pull that specific cell's entry, so yes the string would be good, as long as it returns exactly what is in that cell. Cheers, Toppers wrote: =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric. Do you actual want to SUM these numbers or return them as a string? Given (only) the three cells below, what do you want returned? 8400 8500/8550 8400/8500/8550 "Greg" wrote: Hi, I was wondering whether someone could give me a hand with this formula, or whats wrong with this formula: =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product numbers'!E$3:E$500) The formula works, but it doesnt pull the information i want in column e (at the end of the formula). In column E i have three variables: 8400 8500/8550 8400/8500/8550 the formula only returns the 8400 variable, it doesn't seem to like the / , or any other form of punctuation. Please could someone tell me how it can return these variables with their attached /. thank you Greg. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with Pasting Data | Setting up and Configuration of Excel | |||
| How can I pull data from multiple user workbooks into one? | Excel Discussion (Misc queries) | |||
| Referencing multiple criteria to pull data | Excel Discussion (Misc queries) | |||
| Printing data validation scenarios | Excel Worksheet Functions | |||
| Chart/Source Data update problem | Charts and Charting in Excel | |||
Linear Mode
