Overclock.net › Forums › Software, Programming and Coding › Other Software › GDocs spreadie (Excel-like) formula help needed...
New Posts  All Forums:Forum Nav:

GDocs spreadie (Excel-like) formula help needed...

post #1 of 2
Thread Starter 
Hi,

IF <cell> contains "abc" formula help needed.


What I want to do is to create a formula that looks for a string within the text of a cell; for example, I want the "B" column to give an output that enables a formula (that I will write later) to calculate how many parts are available in a certain depot and region.


The first step I want to take is to get it to count how many of this part are in the "Defective" warehouses, so that I can then exclude those from the other counters...


As it stands, the formula is:
=if(A3="","",if(ISNUMBER(SEARCH("defective",A3))," Defective",IF(ISNUMBER(SEARCH(or("london","dresden ","sweden"),A3)),"EMEA",IF(ISNUMBER(SEARCH("Auckla nd",A3)),"APAC","USA"))))

When I tested that without the OR section, it was working...
=if(A29="","",if(ISNUMBER(SEARCH("defective",A29)) ,"Defective",IF(ISNUMBER(SEARCH("london",A29)),"EM EA",IF(ISNUMBER(SEARCH("Auckland",A29)),"APAC","US A"))))




To make it easier to see/understand, I've copied it to a new file and shared that out:
https://spreadsheets.google.com/spre...=1&output=html


All help gratefully received, REP will be given for all helpful responses.

WKS04
(16 items)
 
WORK_PC
(13 items)
 
LAP03
(13 items)
 
CPUMotherboardGraphicsRAM
i7 920 D0 (HT on) @3.6 GHz Asus P6T nVidia 250 6 GB OCZ Gold 
Hard DriveOptical DriveCoolingOS
250 GB SATA CD DVD RW RASA RS360 Ubuntu 10.04 (W7U x64 as alternate boot) 
MonitorMonitorKeyboardPower
LG 19" (L194WS) LCD HP 15" LCD QWERTY (Logitech) High Power Plus 850W modular 
CaseMouseMouse PadAudio
Jeantech Phong2 Trackball (Logitech) n/a Hitachi AX-M5 
CPUMotherboardGraphicsRAM
Q8400 Dell Optiplex 780 GT430 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB Yes W7 Pro (x86 - with Linux x64 VM) 2 x 24" Dell 
KeyboardPowerCaseMouse
Dell Yeah, got one of those... Dell Logitech Trackball 
Mouse Pad
N/A 
CPUMotherboardGraphicsRAM
T5670 (Core2Duo) HP on-board 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB CD DVD RW W7 Ultimate (x86) 17" 
KeyboardPowerCaseMouse
QWERTY on-board Laptop touchpad 
  hide details  
Reply
WKS04
(16 items)
 
WORK_PC
(13 items)
 
LAP03
(13 items)
 
CPUMotherboardGraphicsRAM
i7 920 D0 (HT on) @3.6 GHz Asus P6T nVidia 250 6 GB OCZ Gold 
Hard DriveOptical DriveCoolingOS
250 GB SATA CD DVD RW RASA RS360 Ubuntu 10.04 (W7U x64 as alternate boot) 
MonitorMonitorKeyboardPower
LG 19" (L194WS) LCD HP 15" LCD QWERTY (Logitech) High Power Plus 850W modular 
CaseMouseMouse PadAudio
Jeantech Phong2 Trackball (Logitech) n/a Hitachi AX-M5 
CPUMotherboardGraphicsRAM
Q8400 Dell Optiplex 780 GT430 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB Yes W7 Pro (x86 - with Linux x64 VM) 2 x 24" Dell 
KeyboardPowerCaseMouse
Dell Yeah, got one of those... Dell Logitech Trackball 
Mouse Pad
N/A 
CPUMotherboardGraphicsRAM
T5670 (Core2Duo) HP on-board 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB CD DVD RW W7 Ultimate (x86) 17" 
KeyboardPowerCaseMouse
QWERTY on-board Laptop touchpad 
  hide details  
Reply
post #2 of 2
Thread Starter 
Le BUMP....
WKS04
(16 items)
 
WORK_PC
(13 items)
 
LAP03
(13 items)
 
CPUMotherboardGraphicsRAM
i7 920 D0 (HT on) @3.6 GHz Asus P6T nVidia 250 6 GB OCZ Gold 
Hard DriveOptical DriveCoolingOS
250 GB SATA CD DVD RW RASA RS360 Ubuntu 10.04 (W7U x64 as alternate boot) 
MonitorMonitorKeyboardPower
LG 19" (L194WS) LCD HP 15" LCD QWERTY (Logitech) High Power Plus 850W modular 
CaseMouseMouse PadAudio
Jeantech Phong2 Trackball (Logitech) n/a Hitachi AX-M5 
CPUMotherboardGraphicsRAM
Q8400 Dell Optiplex 780 GT430 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB Yes W7 Pro (x86 - with Linux x64 VM) 2 x 24" Dell 
KeyboardPowerCaseMouse
Dell Yeah, got one of those... Dell Logitech Trackball 
Mouse Pad
N/A 
CPUMotherboardGraphicsRAM
T5670 (Core2Duo) HP on-board 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB CD DVD RW W7 Ultimate (x86) 17" 
KeyboardPowerCaseMouse
QWERTY on-board Laptop touchpad 
  hide details  
Reply
WKS04
(16 items)
 
WORK_PC
(13 items)
 
LAP03
(13 items)
 
CPUMotherboardGraphicsRAM
i7 920 D0 (HT on) @3.6 GHz Asus P6T nVidia 250 6 GB OCZ Gold 
Hard DriveOptical DriveCoolingOS
250 GB SATA CD DVD RW RASA RS360 Ubuntu 10.04 (W7U x64 as alternate boot) 
MonitorMonitorKeyboardPower
LG 19" (L194WS) LCD HP 15" LCD QWERTY (Logitech) High Power Plus 850W modular 
CaseMouseMouse PadAudio
Jeantech Phong2 Trackball (Logitech) n/a Hitachi AX-M5 
CPUMotherboardGraphicsRAM
Q8400 Dell Optiplex 780 GT430 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB Yes W7 Pro (x86 - with Linux x64 VM) 2 x 24" Dell 
KeyboardPowerCaseMouse
Dell Yeah, got one of those... Dell Logitech Trackball 
Mouse Pad
N/A 
CPUMotherboardGraphicsRAM
T5670 (Core2Duo) HP on-board 4 GB 
Hard DriveOptical DriveOSMonitor
250 GB CD DVD RW W7 Ultimate (x86) 17" 
KeyboardPowerCaseMouse
QWERTY on-board Laptop touchpad 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Other Software
Overclock.net › Forums › Software, Programming and Coding › Other Software › GDocs spreadie (Excel-like) formula help needed...