Hi
I'm using Indirect forumla to give values from different tabs which works perfectly, my search criteria is column R
how do I add a formula to say if cell in column R begins with number 4 then multiple by -1 else just return with value
I'm using Indirect forumla to give values from different tabs which works perfectly, my search criteria is column R
how do I add a formula to say if cell in column R begins with number 4 then multiple by -1 else just return with value
Company budget Lee S.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
3 | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Total | Name | Nominal | ||
4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Head Office 101 | 4100 | ||
5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Head Office 101 | 4300 | ||
6 | 4,474 | 3,421 | 4,474 | 3,158 | 4,474 | 6,053 | 1,842 | 5,000 | 5,263 | 2,632 | 0 | 4,211 | 45000 | Head Office 101 | 5000 | ||
GP Import |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4:D6 | D4 | =INDEX(INDIRECT("'" &$Q4 &"'!C1:C200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
E4:E6 | E4 | =INDEX(INDIRECT("'" &$Q4 &"'!D1:D200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
F4:F6 | F4 | =INDEX(INDIRECT("'" &$Q4 &"'!E1:E200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
G4:G6 | G4 | =INDEX(INDIRECT("'" &$Q4 &"'!F1:F200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
H4:H6 | H4 | =INDEX(INDIRECT("'" &$Q4 &"'!G1:G200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
I4:I6 | I4 | =INDEX(INDIRECT("'" &$Q4 &"'!H1:H200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
J4:J6 | J4 | =INDEX(INDIRECT("'" &$Q4 &"'!I1:I200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
K4:K6 | K4 | =INDEX(INDIRECT("'" &$Q4 &"'!j1:j200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
L4:L6 | L4 | =INDEX(INDIRECT("'" &$Q4 &"'!k1:k200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
M4:M6 | M4 | =INDEX(INDIRECT("'" &$Q4 &"'!l1:l200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
N4:N6 | N4 | =INDEX(INDIRECT("'" &$Q4 &"'!m1:m200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
O4:O6 | O4 | =INDEX(INDIRECT("'" &$Q4 &"'!n1:n200"),MATCH($R4,INDIRECT("'" &Q4 &"'!B1:B200"),0)) |
P4:P6 | P4 | =SUM(D4:O4) |