Formula change with Validation

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Please check the spreadsheet, in the formula =SUMIF($N$11:$N$228,"WIP",$C$11:$C$228), $C$11:$C$228 refers to 2005 and in the formula
=SUMIF($N$11:$N$228,"WIP",$O$11:$O$228) O refers to 2005, I want the formula to work like this, if I change 2005 to 2005 vice versa by validation, I want the formula to change accordingly.
I tried to make this formula, but of course there is something wrong that I dont know what.
=SUMIF(M229="2005",($N$11:$N$228,"TLAND",$C$11:$C$228),($N$11:$N$228,"TLAND",$O$11:$O$228))

Thanks
PP&E Rollforward_All Corps.xls
MNOP
2292,0052,005
230149,545,547LAND155,723,593
23157,861,646WIP69,143,549
232882,513,761BUILDING926,202,813
233593,168,260EQUIPMENT648,423,537
234111,293LEASEHOLDIMP111,293
2353,650,811LANDIMPROVEMENT3,723,778
2361,686,851,3181,803,328,563
SEPTEMBER 2006
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Try this"
=IF(M229="2005",SUMIF($N$11:$N$228,"TLAND",$C$11:$C$228),SUMIF(N$11:$N$228,"TLAND",$O$11:$O$228))

However, be aware that "2005" is NOT the same as "2,005".
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
RalphA,
Thanks
It worked good, I did not know, 2,005 is different from 2005, although using =isnumber() or =istext(), gives same result.

thanks Ralph
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
RalphA,
Thanks
It worked good, I did not know, 2,005 is different from 2005, although using =isnumber() or =istext(), gives same result.

thanks Ralph
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Just remember that text, such as "2,005" means EXACTLY a 2, followed by a comma, folloed by two zeros, followed by a 5, and "2005" means EXACTLY a 2, followed by two zeros, followed by a 5. As you can understand, the text, "2,005" <> "2005". However, the NUMBERs 2,oo5 and 2005 are identical. To Excel, as to us humans, 2000 is identical to 2,005. But, as your English teacher would tell you, "English" is NOT the same as "english". And, to Excel, "2005" is NOT the same as "2005". Have I made the above clear enough? Two texts must be IDENTICAL to be "the same"; anything else, even a comma, or a period, or an interior space in only one of the two, makes them DIFFERENT! To a computer, since it goes by absolutely STRICT logic, just one character different i two texts is no different than you calling a guy "Zachery Taylor", when his name is "Abraham Lincoln", and expecting Old Abe to undestand that you are calling him.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,430
Members
409,876
Latest member
Akash Yadav
Top